MSSQL Blocked Queries
12345SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) where session_id IN ( SELECT blocking_session_id FROM sys.dm_exec_requests WHERE DB_NAME(database_id)=’AIMS’ and blocking_session_id <> 0 )
12345SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) where session_id IN ( SELECT blocking_session_id FROM sys.dm_exec_requests WHERE DB_NAME(database_id)=’AIMS’ and blocking_session_id <> 0 )
Determine the size of database tables in a MSSQL Server Database. 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647SELECT DB_NAME() AS DatabaseNase , GETDATE() AS DateCreated , TableName, Rows …
Determine if a decimal number can be converted to an INT without any loss of precision. Check if either of the rightmost two digits are non-zero 123substring(convert(varchar(10), a.DebitAmount), len(convert(varchar(10), a.DebitAmount)) …
1234567891011121314151617Declare @Table_Name VarChar(100) ,@Column_Name VarChar(100) Set @Table_Name = ” Set @Column_Name = ” Select RowNumber = Row_Number() Over( PARTITION BY T.[Name] Order By T.[Name],C.column_id ), SCHEMA_NAME( T.schema_id ) As SchemaName …
Ensure the user has permission 123USE master; GRANT VIEW SERVER STATE TO username; GO Select from view sys.dm_os_sys_info 1select * from sys.dm_os_sys_info
To see the disk size of database tables in MSSQL: 12345678910111213141516171819202122SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, …
123456789101112131415161718192021222324252627SELECT t.NAME AS TableName, i.name as indexName, p.[Rows], sum(a.total_pages) as TotalPages, sum(a.used_pages) as UsedPages, sum(a.data_pages) as DataPages, …
Syntax for a TSQL date time variable 1234567DECLARE @START_DATE DATETIME DECLARE @END_DATE DATETIME SET @START_DATE = ’09/21/2012 00:00:00′ SET @END_DATE = ’09/21/2012 23:59:59′ SELECT * FROM TABLE WHERE POSTINGDATE >= …
TSQL 123456–all tables and rowcount (in descending order by count) select ‘[‘+SCHEMA_NAME(t.[SCHEMA_ID])+’].[‘+t.NAME+’]’ AS [fulltable_name], SCHEMA_NAME(t.[SCHEMA_ID]) as [schema_name],t.NAME as [table_name] ,i.rows from sys.tables t INNER JOIN sysindexes i ON (t.object_id = …
–Converts an int to a string 123SELECT CONVERT ( varchar , intcolumn ) FROM TABLE