Archive for October, 2015

create index creation scrips

October 15, 2015

This will create a view which you can use to get your create index scripts

 

CREATE VIEW dbo.vw_create_index_scripts

AS
WITH col_list AS (
SELECT DISTINCT object_id,index_id,

STUFF((SELECT ‘, ‘ +QUOTENAME(B.name) + CASE WHEN a.is_descending_key = 1 THEN ‘ DESC ‘ ELSE ” END AS [text()]
FROM SYS.index_columns A
INNER JOIN sys.columns B ON B.column_id = A.column_id AND B.object_id = A.object_id
WHERE A.is_included_column=0 AND a.index_id=z.index_id AND a.object_id=z.object_id
For XML PATH (”)),1,1,”
) col_list
FROM sys.index_columns z ),
include_list AS (
SELECT DISTINCT object_id,index_id,

STUFF((SELECT ‘, ‘ +QUOTENAME(B.name) + CASE WHEN a.is_descending_key = 1 THEN ‘ DESC ‘ ELSE ” END AS [text()]
FROM SYS.index_columns A
INNER JOIN sys.columns B ON B.column_id = A.column_id AND B.object_id = A.object_id
WHERE A.is_included_column=1 AND a.index_id=z.index_id AND a.object_id=z.object_id
For XML PATH (”)),1,1,”
) col_list
FROM sys.index_columns z )

SELECT ‘CREATE ‘ + CASE WHEN a.is_unique =1 THEN ‘ UNIQUE ‘ ELSE ” END +a.type_desc COLLATE DATABASE_DEFAULT+’ INDEX ‘+ a.name + ‘ ON ‘+ QUOTENAME(c.name) + ‘.’+QUOTENAME(b.name)
+ ‘(‘
+ CASE WHEN a.type_desc != ‘NONCLUSTERED COLUMNSTORE’ THEN ISNULL(e.col_list,”) ELSE f.col_list END
+ ‘) ‘
+ CASE WHEN f.col_list IS NOT NULL AND a.type_desc != ‘NONCLUSTERED COLUMNSTORE’ THEN ‘ INCLUDE (‘ + f.col_list + ‘)’ ELSE ” END
+ CASE WHEN filter_definition IS NOT NULL THEN ‘ WHERE ‘ + a.filter_definition ELSE ” END
+’ ON ‘ + QUOTENAME(d.name) script,c.name AS name_schema,b.name name_table,a.name name_index,a.is_unique,a.is_unique_constraint,a.is_primary_key
FROM sys.indexes a
INNER JOIN sys.tables b ON a.object_id = b.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
INNER JOIN sys.data_spaces d ON d.data_space_id = a.data_space_id
LEFT JOIN col_list e ON e.object_id = a.object_id AND e.index_id = a.index_id
LEFT JOIN include_list f ON f.object_id = a.object_id AND f.index_id = a.index_id
WHERE a.type !=0 AND a.is_primary_key =0 AND a.is_unique = 0

UNION ALL

SELECT ‘ALTER TABLE ‘+ QUOTENAME(c.name) + ‘.’+QUOTENAME(b.name )
+ ‘ ADD CONSTRAINT ‘ + QUOTENAME(a.name) + ‘ ‘+ CASE WHEN a.is_primary_key =1 THEN ‘PRIMARY KEY’ WHEN a.is_unique =1 THEN ‘ UNIQUE’ END +’ ‘+a.type_desc COLLATE DATABASE_DEFAULT
+ ‘(‘
+ CASE WHEN a.type_desc != ‘NONCLUSTERED COLUMNSTORE’ THEN ISNULL(e.col_list,”) ELSE f.col_list END
+ ‘) ‘
+ CASE WHEN filter_definition IS NOT NULL THEN ‘ WHERE ‘ + a.filter_definition ELSE ” END
+’ ON ‘ + QUOTENAME(d.name),c.name AS name_schema,b.name name_table,a.name name_index,a.is_unique,a.is_unique_constraint,a.is_primary_key
FROM sys.indexes a
INNER JOIN sys.tables b ON a.object_id = b.object_id
INNER JOIN sys.schemas c ON c.schema_id = b.schema_id
INNER JOIN sys.data_spaces d ON d.data_space_id = a.data_space_id
LEFT JOIN col_list e ON e.object_id = a.object_id AND e.index_id = a.index_id
LEFT JOIN include_list f ON f.object_id = a.object_id AND f.index_id = a.index_id
WHERE a.type !=0 AND (a.is_primary_key =1 OR a.is_unique =1)

Advertisements

which session is using up space in tempdb?

October 2, 2015

SELECT * FROM FU_Catalog.sys.dm_db_session_space_usage