SQL code snippets and helpers

SqlHelpers-01.sql

/*
- Get default constraints of database with definition
- Several scripts to deal with locks in database
- Convert rows to columns in SQL
- SQL to select from XML
- Get table sizes
*/

SqlHelpers-02-DefaultConstrainsDefinition.sql

-- Get default constraints of database with definition
select
	df.object_id,
	df.name 'df',
	object_name(df.parent_object_id) 'tbl',
	c.name 'col'
from sys.default_constraints df
	join sys.columns c on c.object_id = df.parent_object_id
						and c.column_id = df.parent_column_id
where df.[definition] = '(getdate())'

SqlHelpers-03-LocksInDatabase.sql

/* Several scripts to deal with locks */
-- find locks
SELECT  
        t1.resource_type, 
        t1.resource_database_id, 
        t1.resource_associated_entity_id, 
        t1.request_mode, 
        t1.request_session_id, 
        t2.blocking_session_id 
    FROM sys.dm_tran_locks as t1 
    INNER JOIN sys.dm_os_waiting_tasks as t2 
        ON t1.lock_owner_address = t2.resource_address; 
 
-- find accosiated resource
SELECT object_name(object_id), * 
    FROM sys.partitions 
    WHERE hobt_id=<resource_associated_entity_id>
 
--KILL 'session'
 
-- find seession information
select * from sys.dm_exec_sessions where session_id in (66, 87)
 
-- find SQL statement in the session
DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle FROM sys.sysprocesses WHERE spid = 87
SELECT TEXT FROM sys.dm_exec_sql_text(@sqltext)

SqlHelpers-04-RowsToColumns.sql

/*
Convert rows to columns in SQL

ID | TYPE
----------
 1 |  1
 1 |  2
 2 |  1
 convert to 
ID | [1] | [2]
---------------
 1 |  1  |  1
 2 |  1  |  0

 NB: in order to do it dynamicly the columns can be retrieved via XML

select STUFF((SELECT ',' + QUOTENAME(type) 
                    from (SELECT distinct type from @t) t
            FOR XML PATH(''), type).value('.', 'NVARCHAR(MAX)'),1,1,'')
*/
declare @t table (id int, type int)

insert into @t
select * from (values (1,1), (1,2), (2,1)) t(id,type)

select
	id,
	case when [1] is null then 0 else 1 end as [1],
	case when [2] is null then 0 else 1 end as [2]
from @t
pivot 
(
    max(type)
    for type in ([1],[2])
) p 

select STUFF((SELECT ',' + QUOTENAME(type) 
                    from (SELECT distinct type from @t) t
            FOR XML PATH(''), type).value('.', 'NVARCHAR(MAX)'),1,1,'')

SqlHelpers-05-SelectFromXml.sql

/* T-SQL from select from XML */
-- select from 3rd level
declare @d xml
set @d = 
'<list>
    <item><id>1</id></item>
    <item><id>2</id></item>
    <item><id>3</id></item>
    <item><id>4</id></item>
</list>'

SELECT  
	Tbl.Col.value('id[1]', 'INT') as 'id'
FROM @d.nodes('//item') Tbl(Col)

-- select from 2nd level
declare @d xml
set @d = 
'<list>
    <id>1</id>
    <id>2</id>
    <id>3</id>
    <id>4</id>
</list>'

SELECT  
	Tbl.Col.value('.', 'INT') as 'id'
FROM @d.nodes('//id') Tbl(Col)

-- select one attribute
declare @xml1 xml ='<node attr="0"><item attr="D123456" value="1" /></node>'
select @xml1.value('(/node/item/@attr)[1]', 'nvarchar(max)')

SqlHelpers-06-TableSizes.sql

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name