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