| Auto Replacement Key | Replacement |
|---|
| loc | WITH(NOLOCK) |
| sel | SELECT \* FROM |
| gdate | GETDATE() BETWEEN ValidFrom and ValidTo |
| orderb | ORDER BY id DESC |
| ProcExist | IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE object\_id = OBJECT\_ID(N'myproc') AND type IN ( N'P', N'PC' ) ) DROP PROCEDURE myproc GO |
| TableExist | IF (EXISTS (SELECT TOP 1 1 FROM INFORMATION\_SCHEMA.TABLES WHERE TABLE\_NAME = 'TheTable')) BEGIN /\*Do Stuff\*/ END |
| TempTableExist | IF OBJECT\_ID('tempdb..#TheTable') IS NOT NULL BEGIN /\*Do Stuff\*/ END |
| ViewExist | IF EXISTS(SELECT TOP 1 1 FROM sys.views WHERE name = 'TheView') BEGIN /\*Do Stuff\*/ END |
| FunctionExist | IF EXISTS (SELECT TOP 1 1 FROM sys.objects WHERE object\_id = OBJECT\_ID(N'\[dbo\].\[FunctionName\]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )) BEGIN /\* Do your stuff here \*/ END GO |
| IndexExist | IF EXISTS(SELECT TOP 1 1 FROM sys.indexes WHERE name = 'IndexName' AND object\_id = OBJECT\_ID('TableName')) BEGIN /\* Do your stuff here \*/ END |
| DefaultExist | IF NOT EXISTS (SELECT TOP 1 1 FROM sys.all\_columns c JOIN sys.tables t ON t.object\_id = c.object\_id JOIN sys.schemas s ON s.schema\_id = t.schema\_id JOIN sys.default\_constraints d ON c.default\_object\_id = d.object\_id WHERE t.name = 'TABLE\_NAME' AND c.name = 'COLUMN\_NAME' AND s.name = 'dbo') BEGIN /\* Do your stuff here \*/ END |
| CommentChanged | {Date} : {Domain Username} : {Short description of change} |
| CommentCreated | {Date} : {Domain Username} : {Short description of change} |
| StatExec | Use SELECT execquery.last\_execution\_time AS \[Date Time\], execsql.text AS \[Script\] FROM sys.dm\_exec\_query\_stats AS execquery CROSS APPLY sys.dm\_exec\_sql\_text(execquery.sql\_handle) AS execsql ORDER BY execquery.last\_execution\_time DESC |
| StatProc | SELECT TOP 1000 d.object\_id, d.database\_id, OBJECT\_NAME(object\_id, database\_id) 'proc name', d.cached\_time, d.last\_execution\_time, d.total\_elapsed\_time, d.total\_elapsed\_time/d.execution\_count AS \[avg\_elapsed\_time\], d.last\_elapsed\_time, d.execution\_count ,d.last\_elapsed\_time/(1024\*1024) as secondes, \[total\_worker\_time\] FROM sys.dm\_exec\_procedure\_stats AS d ORDER BY d.last\_elapsed\_time desc, \[total\_worker\_time\] DESC; |
| StatSession | SELECT r.start\_time \[Start Time\],session\_ID \[SPID\], DB\_NAME(database\_id) \[Database\], SUBSTRING(t.text,(r.statement\_start\_offset/2)+1, CASE WHEN statement\_end\_offset=-1 OR statement\_end\_offset=0 THEN (DATALENGTH(t.Text)-r.statement\_start\_offset/2)+1 ELSE (r.statement\_end\_offset-r.statement\_start\_offset)/2+1 END) \[Executing SQL\], Status,command,wait\_type,wait\_time,wait\_resource, last\_wait\_type FROM sys.dm\_exec\_requests r OUTER APPLY sys.dm\_exec\_sql\_text(sql\_handle) t WHERE session\_id != @@SPID don't show this query AND session\_id > 50 don't show system queries ORDER BY r.start\_time |
| StatHealth | SELECT TOP 10000 record\_id ,dateadd(ms, - 1 \* (@ms\_ticks\_now - \[timestamp\]), GetDate()) AS EventTime ,SQLProcessUtilization ,SystemIdle ,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization FROM ( SELECT record.value('(./Record/@id)\[1\]', 'int') AS record\_id ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)\[1\]', 'int') AS SystemIdle ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)\[1\]', 'int') AS SQLProcessUtilization ,TIMESTAMP FROM ( SELECT TIMESTAMP ,convert(XML, record) AS record FROM sys.dm\_os\_ring\_buffers WHERE ring\_buffer\_type = N'RING\_BUFFER\_SCHEDULER\_MONITOR' AND record LIKE '%%' ) AS x ) AS y ORDER BY record\_id DESC |
| KillDB | USE master GO DECLARE @kill varchar(8000) = *;* SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';' FROM master..sysprocesses WHERE dbid = db\_id('SignifyHR\_ARMUAT\_CW\_Import\_NVE')
EXEC(@kill);
ALTER DATABASE SignifyHR\_ARMUAT\_CW\_Import\_NVE SET MULTI\_USER WITH ROLLBACK IMMEDIATE GO
|
| ColumnExist | IF EXISTS(SELECT TOP 1 1 FROM sys.columns WHERE Name = N'columnName' AND Object\_ID = Object\_ID(N'schemaName.tableName')) BEGIN Column Exists END |
| ConstraintExist | IF EXISTS (SELECT TOP 1 1 FROM SYS.DEFAULT\_CONSTRAINTS WHERE OBJECT\_NAME(PARENT\_OBJECT\_ID) = 'RequisitionExternalCandidates' AND COL\_NAME(PARENT\_OBJECT\_ID, PARENT\_COLUMN\_ID) = 'CreatedDate') BEGIN /\*Do Stuff\*/ END |
| ScriptName | exec GenerateSQLFileName 'DomainUserName', 'ObjectName', 'SQLType', /\*(Optional)\*/'CommaDelimitedUsedByScripts' |
| RowNum | ROW\_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) |
| ForeignKeyExist | IF NOT EXISTS ( SELECT 1 FROM sys.foreign\_keys AS f
INNER JOIN sys.foreign\_key\_columns AS fc
ON f.OBJECT\_ID = fc.constraint\_object\_id
INNER JOIN sys.tables t
ON t.OBJECT\_ID = fc.referenced\_object\_id
WHERE OBJECT\_NAME (f.referenced\_object\_id) = 'licLicenceReasons'
AND OBJECT\_NAME(f.parent\_object\_id) = 'prsEmployeeLicences'
AND COL\_NAME(fc.parent\_object\_id,fc.parent\_column\_id) = 'licLicenceReasonId'
)
ALTER TABLE prsEmployeeLicences
ADD CONSTRAINT FK\_prsEmployeeLicences\_licLicenceReasons FOREIGN KEY (licLicenceReasonId)
REFERENCES licLicenceReasons(Id)
GO
|
| ObjectExist | /\* C = CHECK constraint D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
- /
IF (OBJECT\_ID('tempdb..#tmpjpImportUpdateData', 'U') IS NOT NULL)
BEGIN
END
|
| SchemaCursor | DECLARE @SchemaID INT DECLARE curs CURSOR FAST\_FORWARD FOR SELECT SchemaID FROM cfgSchemaID WITH(NOLOCK) WHERE GETDATE() BETWEEN ValidFrom AND ValidTo AND SysID = 101
OPEN curs
FETCH NEXT FROM curs INTO @SchemaID
WHILE @@FETCH\_STATUS = 0 BEGIN
/\*Do your commands for @SchemaID here\*/
/\*Get the next author.\*/ FETCH NEXT FROM curs INTO @SchemaID END
CLOSE curs DEALLOCATE curs
|