Useful SQL Server scripts

parent child relation hierachy script

2012-08-31T07:42:00.003-07:00

with cte as
(
select 1 as lvl, SpaceShuttleID , ParentSpaceShuttleID from SpaceShuttle where SpaceShuttleID = 200
union all
select cte.lvl + 1, b.SpaceShuttleID , b.ParentSpaceShuttleID from SpaceShuttle b join cte on cte.SpaceShuttleID = b.ParentSpaceShuttleID
)
select * from cte order by lvl;

modify sysjobs trigger to send alert

2012-08-17T09:29:00.002-07:00

USE [msdb]
GO
/****** Object: Trigger [dbo].[trig_sysjobs_insert_update]    Script Date: 08/17/2012 08:56:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[trig_sysjobs_insert_update]
ON [dbo].[sysjobs]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- Disallow the insert or update if the originating_server_id isn't in sysoriginatingservers_view.
IF (EXISTS (SELECT *
            FROM inserted
           WHERE inserted.originating_server_id NOT IN
                    (SELECT v.originating_server_id
                     FROM sysoriginatingservers_view AS v)))
BEGIN
   RAISERROR(14379, -1, -1, 'dbo.sysjobs')
   ROLLBACK TRANSACTION
    RETURN
END
else
begin

    Declare @Body1 nvarchar(max)
    Select @Body1 = 'SQL Server Job Name: ' + name + ' modified on '+convert(varchar, date_modified,9) from inserted

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients = 'palexander@csod.com',
    @body = @Body1,
    @subject = 'SQL Server Job Modification Alert'

end
END

SQL job queries

2012-08-16T09:50:00.001-07:00

--JOB INFO
select *
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
inner join sysschedules c on b.schedule_id = c.schedule_id
--where a.name like '%SoapFeedJobs%'
where a.name like '%CES Daily Maintenance - VLINE%'

--JOB HISTORY
select *
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'

--JOB HISTORY with outcome result
select
Case isnull(h.run_status,'') WHEN '' THEN 'Unknown' WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration ,
*
from msdb.dbo.sysjobs j
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id AND h.step_id = 0 --AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time
--where j.name like '%SoapFeedJobs-ces_Akamai%'
where j.name like '%CES Daily Maintenance - VLINE%'

lists all the default constraints and the default values for the user tables in the database

2012-08-15T13:04:00.001-07:00

SELECT  
        b.name AS TABLE_NAME,
        d.name AS COLUMN_NAME,
        a.name AS CONSTRAINT_NAME,
        c.text AS DEFAULT_VALUEFROM sys.sysobjects a INNER JOIN
        (SELECT name, id
         FROM sys.sysobjects 
         WHERE xtype = 'U') b on (a.parent_obj = b.id)
                      INNER JOIN sys.syscomments c ON (a.id = c.id)
                      INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)                                          
 WHERE a.xtype = 'D'        
 ORDER BY b.name, a.name

function to get time for next job

2012-08-15T12:46:00.001-07:00

use msdb
go

/*
select dbo.fn_next_job_time(15, null, null)
select dbo.fn_next_job_time(7, null, null)
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -','Weekend Sat')
select dbo.fn_next_job_time(7, 'CES Weekly Maintenance -',null)
select dbo.fn_next_job_time(4, 'CES Weekly Maintenance -',null)
*/
CREATE FUNCTION fn_next_job_time
(
    @interval int = 10,
    @jobname nvarchar(100) = null ,
    @schedulename nvarchar(100) = null
)
RETURNS int
AS
BEGIN

    set @jobname = @jobname + '%'
    set @schedulename = @schedulename + '%'

    declare @curr_hr int, @curr_min int, @curr_sec int, @next_hr int, @next_min int, @next_sec int
    declare @nexttime int
    --set @nexttime = 10004
    --set @nexttime = 206
    --set @nexttime = 5506
    --set @nexttime = 230847
    --set @nexttime = 145712
    --set @nexttime = 41942
    --set @nexttime = 225847
    --set @nexttime = 235847

    select top 1 @nexttime = c.active_start_time
    --a.schedule_id, b.name, c.active_start_time,c.name, *
    from sysjobschedules a
    inner join sysjobs b on a.job_id = b.job_id
    inner join sysschedules c on a.schedule_id = c.schedule_id
    where
    (b.name like @jobname or @jobname is null)
    and
    ( c.name like @schedulename or @schedulename is null)
    order by c.active_start_time desc

    select @curr_hr = right(convert(nvarchar(10), @nexttime/10000),2) , @curr_min = right(convert(nvarchar(10), @nexttime/100),2), @curr_sec = right(convert(nvarchar(10), @nexttime),2)
    set @next_sec = @curr_sec
    set @next_min = (@curr_min + @interval)%60
    set @next_hr = @curr_hr + (@curr_min + @interval)/ 60
    set @next_hr = case when @next_hr >= 24 then 0 else @next_hr end
    --select @nexttime, @curr_hr , @curr_min , @curr_sec
    --select @nexttime, @next_hr , @next_min , @next_sec

    select @nexttime = convert(int,
    (case when @next_hr < 10 then '0'+CONVERT(nvarchar(10),@next_hr ) else convert(nvarchar(10),@next_hr ) end) +
    (case when @next_min < 10 then '0'+CONVERT(nvarchar(10),@next_min ) else convert(nvarchar(10),@next_min ) end) +
    (case when @next_sec < 10 then '0'+CONVERT(nvarchar(10),@next_sec) else convert(nvarchar(10),@next_sec) end)
    )


    RETURN @nexttime

END
GO

list spaces used by all DBs in a server : sp_dbspaceall

2012-07-18T08:33:00.001-07:00

use master
go
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dbspaceall]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_dbspaceall]
GO
/*
exec sp_dbspaceall
*/
create procedure sp_dbspaceall
as
begin

Declare @FileSpace table (
[database_id] int,
[db_name] nvarchar(100),
[file_id] int,
[file_name] nvarchar(100),
[space_used] bigint
)
INSERT INTO @FileSpace EXEC sp_MSforeachdb 'USE [?] SELECT db_id(''?''),''?'' as DBname,fileid, name ,FILEPROPERTY(name, ''SpaceUsed'') as spaceused from sys.sysfiles';

With SpaceInfo (DBName, FileName, FileType,FilePath,CurrentSizeMB,SpaceUsedMB)
as
(

SELECT
DB_NAME(df.[database_id]) as [DBName],
df.[name] as [FileName] ,
df.type_desc as [FileType],
df.physical_name as [FilePath],
(df.[size]*8.0)/1024.0 as [CurrentSizeMB],
(fs.[space_used]*8.0)/1024.0 as [SpaceUsedMB]
FROM
SYS.MASTER_FILES AS df
INNER JOIN @FileSpace as fs on (df.[database_id]= FS.[database_id] and df.[file_id]=fs.[file_id])
)
SELECT
SI.DBName,
convert(dec(17,2),SUM(SI.CurrentSizeMB)) as [DatabaseSizeMB],
convert(dec(14,2),(1-(SUM(SI.SpaceUsedMB)/SUM(SI.CurrentSizeMB)))*100 )as [%age Free],
convert(dec(17,2),D.DataFileSizeMB) as DataFileSizeMB,
convert(dec(14,2) ,(1-(D.DataFileSpaceUsedMB/D.DataFileSizeMB))*100) as [%age Free],
convert(dec(17,2),L.LogFileSizeMB) as LogFileSizeMB,
convert(dec(14,2),(1-(L.LogFileSpaceUsedMB/L.LogFileSizeMB))*100) as [%age Free]
FROM
SpaceInfo SI
CROSS APPLY (SELECT sum(CurrentSizeMB) as [DataFileSizeMB], SUM(SpaceUsedMB) as [DataFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype='ROWS' and DBName=SI.DBName group by DBName) as D
CROSS APPLY (SELECT sum(CurrentSizeMB) as [LogFileSizeMB], SUM(SpaceUsedMB) as [LogFileSpaceUsedMB] FROM SpaceInfo
WHERE filetype ='LOG' and DBName=SI.DBName group by DBName) as L
GROUP BY
SI.DBName, D.DataFileSizeMB,D.DataFileSpaceUsedMB,L.LogFileSizeMB,L.LogFileSpaceUsedMB
Order by D.DataFileSizeMB desc
end

SQL Query Stress Tool

2012-07-18T06:59:00.001-07:00

http://www.mssqltips.com/sqlservertip/2730/sql-query-stress-tool/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012717

Top SQL Server Tools SQL compliance manager SQL diagnostic manager SQL Monitor SQL Sentry Performance Advisor for SQL Server SQL safe backup SSAS Interview Questions on Measures, Actions, and Storage

2012-07-16T10:15:00.005-07:00

http://www.mssqltips.com/sqlservertip/2637/ssas-interview-questions-part-i-questions-on-basic-concepts-data-sources-and-data-source-views/

http://www.mssqltips.com/sqlservertip/2662/sql-server-analysis-services-interview-questions-part-ii--dimensions/

http://www.mssqltips.com/sqlservertip/2683/sql-server-analysis-services-interview-questions-on-dimensions-hierarchies-and-properties/

http://www.mssqltips.com/sqlservertip/2614/ssas-interview-questions-on-measures-actions-and-storage/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012715

Importing SQL Server Data Using SSIS - Which Option is Fastest

2012-07-13T14:27:00.001-07:00

http://www.mssqltips.com/sqlservertip/2684/importing-sql-server-data-using-ssis--which-option-is-fastest/

Preventing from Dropping database

2012-07-11T09:09:00.002-07:00

CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]
ON ALL SERVER
FOR DROP_DATABASE
AS

DECLARE @db VARCHAR(800)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)')) + '

To temporarily allow dropping action:
DISABLE TRIGGER ddl_trig_Prevent_Drop_Database ON ALL SERVER;

!!!!!!MAKE SURE TO RE-ENABLE THE TRIGGER CHECK AS SOON AS YOU ARE DONE!!!!!!
ENABLE TRIGGER ddl_trig_Prevent_Drop_Database ON ALL SERVER;
'
RAISERROR(@db, 16, 1)WITH LOG

ROLLBACK

GO

Disable Enable indexes

2012-07-03T10:44:00.001-07:00

select
    sys.objects.name,
    sys.indexes.name,
    'alter index '+ sys.indexes.name +' on '+sys.objects.name+' rebuild',
    'alter index '+ sys.indexes.name +' on '+sys.objects.name+' disable'
from sys.indexes
    inner join sys.objects on sys.objects.object_id = sys.indexes.object_id
where sys.indexes.is_disabled = 1
order by
    sys.objects.name,
    sys.indexes.name

Ordering and Ranking a data set

2012-06-11T10:40:00.000-07:00

useful technique to pre-order data set

select
DENSE_RANK() over (order by name_first),
RANK() over (order by name_first),
ROW_NUMBER() over (order by name_first),
ROW_NUMBER() over (order by name_last),
*
from users
order by name_first, name_last

Database Encryption with DB Certificates

2012-05-03T09:06:00.001-07:00

SQL Servers Database Encryption on Swim lanes

This document is create on May 2^nd, 2012 and is valid for Swim Lanes. Certificates are installed on LD4PRDCLT101 and this version of the certificate and key should be used across all swim lanes if needed.

Create a new Master Key

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';

--To Drop the masterkey

--DROP MASTER KEY

Create a new Certificate per Database that need to be encrypted

--database encryption key

CREATE CERTIFICATE DatabaseName1_Cert WITH SUBJECT = 'DatabaseName1 Certificate'

CREATE CERTIFICATE DatabaseName2_Cert WITH SUBJECT = 'DatabaseName2 Certificate'

CREATE CERTIFICATE DatabaseName3_Cert WITH SUBJECT = 'DatabaseName3 Certificate'

--To Drop the certificate

-- DROP CERTIFICATE DatabaseName1_Cert

Backup Master Key and certificates and use for restoring databases on a different server

Use master

Backup master key to file = 'C:\MasterKey.key' encryption by password = 'Password' ;

BACKUP CERTIFICATE DatabaseName1_Cert TO FILE = 'c:\DatabaseName1_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName1_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

BACKUP CERTIFICATE DatabaseName2_Cert TO FILE = 'c:\DatabaseName2_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName2_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

BACKUP CERTIFICATE DatabaseName3_Cert TO FILE = 'c:\DatabaseName3_Cert_file.cert'

WITH PRIVATE KEY ( FILE = 'c:\DatabaseName3_Cert_key.cert' ,

ENCRYPTION BY PASSWORD = 'Password'

);

Create certificates from a backup certificate on another server, (note: the master key should be created before this can be done)

CREATE CERTIFICATE DatabaseName1_Cert

FROM FILE = 'C:\DatabaseName1_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName1_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName2_Cert

FROM FILE = 'C:\DatabaseName2_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName2_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

CREATE CERTIFICATE DatabaseName3_Cert

FROM FILE = 'C:\DatabaseName3_Cert_file.cert'

WITH PRIVATE KEY (FILE = 'C:\DatabaseName3_Cert_key.cert' , DECRYPTION BY PASSWORD = 'Password')

Encrypt a Database

USE [DatabaseName1]

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName1_Cert

ALTER DATABASE [DatabaseName1]

SET ENCRYPTION ON

USE [DatabaseName2]

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName2_Cert

ALTER DATABASE [DatabaseName2]

SET ENCRYPTION ON

USE [DatabaseName3]

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE DatabaseName3_Cert

ALTER DATABASE [DatabaseName3]

SET ENCRYPTION ON

Restore the Databases on a separate server, the key and certificates should be already created and restored. If the certificates are not created properly the restore will fail.

RESTORE DATABASE [DatabaseName1_retored]

FROM DISK = N'c:\DatabaseName1.bak' WITH FILE = 1,

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName1.mdf',

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName1.LDF',

NOUNLOAD, REPLACE, STATS = 10

RESTORE DATABASE [DatabaseName2_retored]

FROM DISK = N'c:\DatabaseName2.bak' WITH FILE = 1,

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName2.mdf',

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName2.LDF',

NOUNLOAD, REPLACE, STATS = 10

RESTORE DATABASE [DatabaseName3_retored]

FROM DISK = N'c:\DatabaseName3.bak' WITH FILE = 1,

MOVE N'Data' TO N'E:\DATA_Release\DatabaseName3.mdf',

MOVE N'Log' TO N'E:\LOG_Release\DatabaseName3.LDF',

NOUNLOAD, REPLACE, STATS = 10

View which Databases are encrypted

SELECT db_name(database_id), encryption_state, *

FROM sys.dm_database_encryption_keys with (nolock)

System Tables to view the encryption keys

SELECT * FROM sys.symmetric_keys

SELECT * FROM sys.certificates

SELECT * FROM sys.dm_database_encryption_keys

Remove Encryption from Database

ALTER DATABASE [DatabaseName1];

SET ENCRYPTION OFF;

use [DatabaseName1]

DROP DATABASE ENCRYPTION KEY;

list of failed jobs

2012-04-25T09:04:00.001-07:00

SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

Querying the SQL Server System Catalog FAQ

2012-04-23T14:52:00.001-07:00

How do I find all the tables that do not have a clustered index in a specified database?

Before you run the following queries, replace with a valid database name.

Useful SQL Server scripts

parent child relation hierachy script

modify sysjobs trigger to send alert

SQL job queries

lists all the default constraints and the default values for the user tables in the database

function to get time for next job

list spaces used by all DBs in a server : sp_dbspaceall

SQL Query Stress Tool

Top SQL Server Tools SQL compliance manager SQL diagnostic manager SQL Monitor SQL Sentry Performance Advisor for SQL Server SQL safe backup SSAS Interview Questions on Measures, Actions, and Storage

Importing SQL Server Data Using SSIS - Which Option is Fastest

Preventing from Dropping database

Disable Enable indexes

Ordering and Ranking a data set

Database Encryption with DB Certificates

list of failed jobs

Querying the SQL Server System Catalog FAQ

How do I find all the tables that do not have a clustered index in a specified database?

How do I find all the owners of entities contained in a specified schema?

How do I find all the tables that do not have a primary key?

How do I find all the tables that do not have an index?

How do I find all the tables that have an identity column?

How do I find the data types of the columns of a specified table?

How do I find the dependencies on a specified function?

How do I find all the stored procedures in a database?

How do I find the parameters for a specified stored procedure or function?

How do I find all the user-defined functions in a database?

How do I find all views in a database?

How do I find all the entities that have been modified in the last N days?

How do I find the LOB data types of a specified table?

How do I view the definition of a module?

How do I view the definition of a server-level trigger?

How do I find the columns of a primary key for a specified table?

How do I find the columns of a foreign key for a specified table?

How do I find the permissions granted or denied to a specified principal?

How do I determine if a column is used in a computed column expression?

How do I find all the columns that are used in a computed column expression?

How do I find the columns that depend on a specified CLR user-defined type or alias type?

How do I find the computed columns that depend on a specified CLR user-defined type or alias type?

How do I find the parameters that depend on a specified CLR user-defined type or alias type?

How do I find the CHECK constraints that depend on a specified CLR user-defined type?

How do I find the views, Transact-SQL functions, and Transact-SQL stored procedures that depend on a specified CLR user-defined type or alias type?

How do I find all the constraints for a specified table?

How do I find all the indexes for a specified table?

How do I find all the objects that have a specified column name?

How do I find all the user-defined tables in a specified database?

How do I find all the tables and indexes that are partitioned?

How do I find all the statistics on a specified object?

How do I find all the statistics and statistics columns on a specified object?

How do I find the definition of a view?

View / Grant / Revoke Security permissions

SQL Server Service Broker example on how to configure, send and receive messages

Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE

New columnstore index feature in SQL Server 2012

SQL Jobs with Multiple schedules

Verify SQL Server Stored Procedures are the same on multiple servers

Long Running Queries Alert

Long Runing Queries on '+@@SERVERNAME+' executing over '+convert(nvarchar(10),@longduration)+' minutes

If Primary Key Exists

Proc to show expensive queries : sp_expensive_queries

Automate Monitoring SQL Server Error Logs with Email Alerts