Table Index Store and Create

,

The code will save all metadata information from a Table or database for restoring indexes and user created stats. Many times you want to restore a database or table,

This script will save all metadata related to the indexes for future used or storage.

if not exists ( select * from sysobjects where ID =object_id(N'tblDBAIdxMetadt') and ObjectProperty(ID,'IsTable')=1)
CREATE TABLE [dbo].[tblDBAIdxMetadt](
	[TableId] [int] NOT NULL,
	[SchemaOwner] [varchar](25) NOT NULL,
	[TableName] [varchar](255) NOT NULL,
	[IndexID] [int] NOT NULL,
	[IsStatistics] [bit] NOT NULL DEFAULT ('FALSE'),
	[IsClustered] [bit] NOT NULL DEFAULT ('FALSE'),
	[IndexName] [varchar](255) NOT NULL,
	[ClusterType] [varchar](50) NOT NULL,
	[UniqueType] [varchar](50) NULL,
	[IndexType] [varchar](50) NULL,
	[AllColName] [varchar](2000) NULL
) ON [PRIMARY]

GO

DECLARE @MyID int
        ,@TableName varchar(255)
set @TableName = ---TableName Here             
SET @MyID = (SELECT OBJECT_ID(@TableName,'U'))  

INSERT INTO [dbo].[tblDBAIdxMetadt]
           ([TableId]
           ,[SchemaOwner]
           ,[TableName]
           ,[IndexID]
           ,[IsStatistics]
           ,[IsClustered]
           ,[IndexName]
           ,ClusterType
           ,[UniqueType]
           ,[IndexType]
           ,[AllColName])
SELECT 
obj.id AS TableID
,usr.name Owner
,obj.name TableName
,idx.Indid AS IndexID
,CASE 
 WHEN (idx.STATUS & 64) <> 0 THEN 1
 ELSE 0 end as IsStatistics
,Case idx.indid
  when 1 then 1
  else 0 end as IsClustered
 , CASE idx.name
  WHEN obj.name THEN '** NONE **' 
 ELSE idx.name END AS IndexName, 
  CASE idx.indid
  WHEN 1 THEN 'CLUSTERED' 
  WHEN 0 THEN 'HEAP' 
   ELSE 'NONCLUSTERED' END AS ClusterType,
            CASE
               WHEN (idx.status & 2048) > 0 THEN 'PRIMARY KEY' 
                  WHEN (idx.status & (2|4096)) > 0 THEN 'UNIQUE'
              
            ELSE '' END AS UniqueType,
         CASE       
                  WHEN (idx.status & (2048)) > 0 
                              OR ((idx.status & (4096)) > 0 )
                        THEN 'CONSTRAINT' 
                  WHEN idx.indid = 0 THEN ' '
            ELSE 'INDEX' END AS IndexType,

            CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 1) IS NULL THEN '' 
            ELSE '['+ INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 1)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 2) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,2)+']' END +
            CASE       
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 3) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,3)+']' END +
         CASE       
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 4) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,4)+']' END +
            CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 5) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,5)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 6) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,6)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 7) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 7)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid, 8) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,8)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 9) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,9)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 10) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,10)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 11) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,11)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 12) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,12)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 13) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,13)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 14) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,14)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 15) IS NULL THEN '' 
            ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,15)+']' END +
      CASE       
                  WHEN INDEX_COL(usr.name+'.'+ QUOTENAME(obj.name), idx.indid, 16) IS NULL THEN '' 
          ELSE ',['+INDEX_COL(usr.name+'.'+QUOTENAME(obj.name), idx.indid,16)+']' END AS AllColName
     

 FROM sysobjects obj (NOLOCK)
      LEFT OUTER JOIN sysindexes idx (NOLOCK)
            ON obj.id = idx.id
     JOIN sysusers usr (NOLOCK) 
            ON obj.uid = usr.uid 
 WHERE obj.type = 'U' AND idx.indid < 255
 AND obj.name NOT IN ('dtproperties')
AND idx.name  NOT LIKE '_WA_Sys_%'
And obj.id = @MyID --Removed if want to query all tables
end

Rate

4.67 (3)

Share

Share

Rate

4.67 (3)