SELECT o.[name] AS [Table Name]
,i.[name] AS [Index
Name]
,i.[fill_factor] AS
[Existing Fill Factor]
,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE
OBJECT_id=OBJECT_ID('''+QUOTENAME(s.name) +'.' + QUOTENAME(o.name)+'''' +') AND name = ''' + (Replace(Replace(i.name,'[',''),']','')) +''' )
DROP INDEX ' + QUOTENAME(i.[name]) + ' ON '
+ QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(s.[name]) +'.' + QUOTENAME(o.[name]) + ' '
+ 'WITH ( ONLINE = OFF )'
AS [DROP INDEX Statement]
FROM [sys].[indexes] i
INNER JOIN [sys].[objects] o
ON i.[object_id] = o.[object_id]
AND o.[type] = 'U' AND i.[name] <> 'NULL'
INNER JOIN sys.[schemas] s
ON o.[schema_id] = s.[schema_id]
INNER JOIN [sys].[stats] st
ON i.[name] COLLATE Latin1_General_CI_AS = st.[name] COLLATE Latin1_General_CI_AS
AND o.[object_id] = st.[object_id]
AND o.[type] COLLATE Latin1_General_CI_AS = 'U'
WHERE i.[name] NOT IN (SELECT i.[name]
FROM [sys].[objects] o
INNER JOIN [sys].[indexes]
i
ON o.[object_id] = i.[object_id]
INNER JOIN [sys].[columns]
c
ON o.[object_id] = c.[object_id]
WHERE c.[is_identity] = 1
AND o.[type] = 'U'
AND i.[type_desc] = 'CLUSTERED')
AND o.name NOT LIKE 'sys%'
ORDER BY o.[name]
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
