Not logged in - Login

Retrieve Table Information

The following script can be used to pull the column and index information for a table.

declare @sTableName   sysname   
-------------------------------------
-- set the table name here
-------------------------------------
set @sTableName = 'PutYourTableNameHere'


declare @lObjectID int
set @lObjectID = object_id(@sTableName)

declare @sTab varchar(5), @sCrlf varchar(5)
set @sTab = char(9)
set @sCrlf = char(13) + char(10)

declare @sTableFields varchar(max)   -- hold the fields for the create statement
set @sTableFields = ''

-- declare & open a cursor to iterate thru all of the columns
DECLARE curAllColumns cursor fast_forward for
   select [name], iscomputed from syscolumns with (nolock) where [id] = @lObjectID order by colorder
OPEN curAllColumns

-- declare variables to hold the data from our cursor   
DECLARE @sColName sysname
   , @bIsComputed bit

-- get the 1st row of data from the cursor
FETCH next from curAllColumns into @sColName, @bIsComputed

-- keep going while the last fetch was good
WHILE (@@fetch_status = 0)
begin

   set @sTableFields = @sTableFields + @sCrlf + @sTab + ', ' + @sColName
   
   declare @lSysTypeID int

   if (@bIsComputed = 0)
   begin

      -- get the field type- int, varchar, text, etc.
      select @sTableFields = @sTableFields + @sTab + b.name
         , @lSysTypeID = b.system_type_id
      from syscolumns a with (nolock) 
      inner join (select * from sys.types with (nolock) where [name] <> 'sysname') b on a.xtype = b.system_type_id
      where a.[id] = @lObjectID and a.[name] = @sColName

      -- get the length and/or precision, if necessary
      select @sTableFields = @sTableFields + 
         case a.xtype
            when 173 then '(' + convert(varchar, a.length) + ')'   -- binary
            when 175 then '(' + convert(varchar, a.length) + ')'   -- char
            when 106 then '(' + convert(varchar, a.xprec) + ', ' + convert(varchar, a.xscale) + ')'   -- decimal
            when 239 then '(' + convert(varchar, a.length) + ')'   -- nchar
            when 108 then '(' + convert(varchar, a.xprec) + ', ' + convert(varchar, a.xscale) + ')'   -- numeric
            when 231 then '(' + 
               --convert(varchar, a.length) 
               case when a.length = -1 then 'max' else convert(varchar, a.length) end
               + ')'   -- nchar
            when 165 then '(' + 
               --convert(varchar, a.length) 
               case when a.length = -1 then 'max' else convert(varchar, a.length) end
               + ')'   -- varbinary
            when 167 then '(' + 
               --convert(varchar, a.length) 
               case when a.length = -1 then 'max' else convert(varchar, a.length) end
               + ')'   -- varchar
            else ''
         end
      from syscolumns a with (nolock) 
      --inner join sys.types b with (nolock) on a.xtype = b.system_type_id
      where a.[id] = @lObjectID and a.[name] = @sColName

      -- is the column an identity column
      select @sTableFields = @sTableFields + ' identity(' 
         + convert(varchar, c.seed_value) 
         + ', '
         + convert(varchar, c.increment_value)
         + ')'            
      from syscolumns a with (nolock) 
      inner join sys.identity_columns c with (nolock) on a.id = c.object_id and a.colid = c.column_id
      where a.[id] = @lObjectID and a.[name] = @sColName
         and c.is_identity = 1

      -- is the column nullable?
      select @sTableFields = @sTableFields + ' not null' 
      from syscolumns a with (nolock) 
      where a.[id] = @lObjectID and a.[name] = @sColName
         and a.isnullable = 0

      -- set the default, if any
      select @sTableFields = @sTableFields + ' default' + d.definition
      from syscolumns a with (nolock) 
      --inner join sys.default_constraints d with (nolock) on a.id = d.parent_object_id
      inner join sys.default_constraints d with (nolock) on a.cdefault = d.object_id
      where a.[id] = @lObjectID and a.[name] = @sColName
         and a.colid = d.parent_column_id

   end else begin

      -- we have a computed column
      select @sTableFields = @sTableFields + ' as ' + b.definition
      from syscolumns a with (nolock) 
      inner join sys.computed_columns b with (nolock) on a.id = b.object_id and a.colid = b.column_id
      where a.[id] = @lObjectID and a.[name] = @sColName

   end

   -- get the next row of data from the cursor
   FETCH next from curAllColumns into @sColName, @bIsComputed
end


-- close & deallocate the cursor
CLOSE curAllColumns
DEALLOCATE curAllColumns


-- output the list of columns
print @sTableFields


-- get the primary key index, if any.
if (exists(select 1 from sys.indexes a where a.[object_id] = @lObjectID and a.is_primary_key = 1))
begin
   set @sTableFields = ''
      
   select 
      @sTableFields = @sTableFields + 
         case
            when len(@sTableFields) = 0 then @sCrlf + @sTab + ', primary key ' + ltrim(rtrim(a.type_desc)) + ' (' + c.[name] 
            else ', ' + c.[name]
         end
         + 
         case 
            when b.is_descending_key = 1 then ' desc' else ''
         end
   from sys.indexes a
   inner join sys.index_columns b on a.[object_id] = b.[object_id] and a.index_id = b.index_id
   inner join sys.all_columns c on b.[object_id] = c.[object_id] and b.column_id = c.column_id
   where a.[object_id] = @lObjectID and a.is_primary_key = 1
   order by b.key_ordinal

   set @sTableFields = @sTableFields + ')'

   print @sTableFields

end

-- get the unique indexes, if any
if (exists(select 1 from sys.indexes a where a.[object_id] = @lObjectID and a.is_unique_constraint = 1))
begin
   set @sTableFields = ''

      
   select 
      @sTableFields = @sTableFields + 
         case
            when b.key_ordinal = 1 and len(@sTableFields) = 0 then @sCrlf + @sTab + ', unqiue ' + ltrim(rtrim(a.type_desc)) + ' (' + c.[name] 
            when b.key_ordinal = 1 and len(@sTableFields) > 0 then ')' + @sCrlf + @sTab + ', unqiue ' + ltrim(rtrim(a.type_desc)) + ' (' + c.[name] 
            else ', ' + c.[name]
         end
         + 
         case 
            when b.is_descending_key = 1 then ' desc' else ''
         end
   from sys.indexes a
   inner join sys.index_columns b on a.[object_id] = b.[object_id] and a.index_id = b.index_id
   inner join sys.all_columns c on b.[object_id] = c.[object_id] and b.column_id = c.column_id
   where a.[object_id] = @lObjectID and a.is_unique_constraint = 1
   order by b.key_ordinal
      
   set @sTableFields = @sTableFields + ')'

   print @sTableFields
end

-- get the other indexes, if any
if (exists(select 1 from sys.indexes a where a.[object_id] = @lObjectID and a.is_primary_key = 0 and a.is_unique_constraint = 0))
begin

   -- object_name(1474104292)
   declare @index_id int = -1
      , @idxname varchar(100)
   declare @sIdxTemplate varchar(300) = @sCrLf + @sCrLf + 'if exists (select * from dbo.sysindexes where name = N''[INDEXNAME]'' and id = object_id(N''[TABLENAME]''))
   drop index [TABLENAME].[INDEXNAME]
GO
'

   -- declare & open a cursor
   DECLARE curIdx cursor fast_forward for
      select a.index_id, a.[name]
      from sys.indexes a
      where a.[object_id] = @lObjectID and a.is_primary_key = 0 and a.is_unique_constraint = 0 
   OPEN curIdx

   -- get the first row from the cursor
   FETCH next from curIdx into @index_id, @idxname

   -- keep going while the last fetch was good
   while (@@FETCH_STATUS = 0)
   begin
      set @sTableFields = REPLACE(@sIdxTemplate, 'TABLENAME', OBJECT_NAME(@lObjectID))
      select @sTableFields = REPLACE(@sTableFields, 'INDEXNAME', @idxname)

      select @sTableFields = @sTableFields + 'CREATE '
         + 
         case 
            when a.is_unique = 1 then 'UNIQUE ' 
            else ''
         end
         +
         a.type_desc + ' INDEX [' + @idxname + '] ON [' + OBJECT_NAME(@lObjectID) + ']' + @sCrlf + '('
      from sys.indexes a
      where a.[object_id] = @lObjectID and a.index_id = @index_id

      select 
         @sTableFields = @sTableFields + 
            case
               when b.key_ordinal = 1 then @sCrlf + @sTab + c.[name]
               else @sCrlf + @sTab + ', ' + c.[name]
            end
            + 
            case 
               when b.is_descending_key = 1 then ' desc' else ''
            end
      from sys.indexes a
      inner join sys.index_columns b on a.[object_id] = b.[object_id] and a.index_id = b.index_id
      inner join sys.all_columns c on b.[object_id] = c.[object_id] and b.column_id = c.column_id
      where a.[object_id] = @lObjectID and a.index_id = @index_id
         and b.is_included_column = 0
      order by a.index_id, b.is_included_column, b.key_ordinal

      set @sTableFields = @sTableFields + @sCrlf + ')'

      -- add the included columns, if any
      if (exists(
         select 1 
         from sys.indexes a 
         inner join sys.index_columns b on a.[object_id] = b.[object_id] and a.index_id = b.index_id
         where a.[object_id] = @lObjectID and a.index_id = @index_id and b.is_included_column = 1))
      begin
         ;with cteA as (
            select rownum = ROW_NUMBER() over (order by b.index_column_id)
               , c.[name]
               , b.is_descending_key
               , b.index_column_id
            from sys.indexes a
            inner join sys.index_columns b on a.[object_id] = b.[object_id] and a.index_id = b.index_id
            inner join sys.all_columns c on b.[object_id] = c.[object_id] and b.column_id = c.column_id
            where a.[object_id] = @lObjectID and a.index_id = @index_id
               and b.is_included_column = 1
         )
         select 
            @sTableFields = @sTableFields +
               case
                  when rownum = 1 then @sCrlf + 'INCLUDE (' + a.[name]
                  else ', ' + a.[name]
               end
         from cteA a
         order by a.index_column_id

         set @sTableFields = @sTableFields + ')'
      end

      -- close the index statement
      set @sTableFields = @sTableFields + @sCrlf + 'ON [PRIMARY]' + @sCrlf + 'GO' + @sCrlf

      -- send what we have to the output window
      print @sTableFields

      -- get the next row from the cursor
      FETCH next from curIdx into @index_id, @idxname
   end


   -- close & deallocate the cursor
   CLOSE curIdx
   DEALLOCATE curIdx

end

Sample output:

   , lRowID   int identity(1000, 1) not null
   , sInfo1   varchar(30) not null
   , sInfo2   varchar(44)
   , lInfo1   int not null
   , lInfo2   int not null
   , sLastUser   varchar(50) not null default(left(suser_sname(),(50)))
   , dtLastMod   datetime not null default(getdate())
   , sCreatedBy   varchar(50) not null default(left(suser_sname(),(50)))
   , dtCreateDate   datetime not null default(getdate())
   , Stamp   timestamp not null
   , uuid   uniqueidentifier not null default(newid())

   , primary key NONCLUSTERED (uuid, lRowID)

   , unqiue CLUSTERED (lInfo1)
   , unqiue NONCLUSTERED (uuid, lInfo2 desc, lRowID)


if exists (select * from dbo.sysindexes where name = N'[IX_TestTable2_1]' and id = object_id(N'[TestTable2]'))
   drop index [TestTable2].[IX_TestTable2_1]
GO
CREATE NONCLUSTERED INDEX [IX_TestTable2_1] ON [TestTable2]
(
   lInfo1
)
INCLUDE (sInfo1, sInfo2)
ON [PRIMARY]
GO


if exists (select * from dbo.sysindexes where name = N'[IX_TestTable2_2]' and id = object_id(N'[TestTable2]'))
   drop index [TestTable2].[IX_TestTable2_2]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_TestTable2_2] ON [TestTable2]
(
   dtCreateDate
   , lRowID desc
)
INCLUDE (sInfo1, sInfo2)
ON [PRIMARY]
GO