Script tables to emulate Copy in Enterprise Manage
This script will produce the same output as doing a Copy in enterprise manager and then pasting into Query Analyser.
It can handle Clustered and Nonclustered Primary Keys, Multiple Foreign Keys, Defaults, Constraints, Identity Fields. If you are able to improve this script, or find any bugs, please let me know.
create proc usp_scripttables @script_table varchar(256) as
begin
set nocount on
declare @mincols int
declare @maxcols int
declare @count int
declare @statement varchar(8000)
declare @table_name varchar(256)
declare @table_id int
declare @ptr binary(16)
declare @txtlen int
declare @default varchar(8000)
declare @default_name varchar(256)
declare @default_id int
declare @status int
declare @clustered int
declare @indid int
declare @ftable_id int
declare @ftable_name varchar(256)
declare @iscomputed int
declare @incr varchar(10)
declare @seed varchar(10)
select @table_name = @script_table
select @table_id = id from sysobjects
where name = @table_name
and xtype = 'U'
if @table_id <> 0
begin
--min no of columns in the table
select @mincols = min(colid) from syscolumns
where id = @table_id
--max no of columns in the table
select @maxcols = max(colid) from syscolumns
where id = @table_id
create table #sql_statements (sql_id int identity (1,1), string text)
select @statement = 'CREATE TABLE [' + @table_name + '] (' + char(13)
insert into #sql_statements (string) values(@statement)
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
select @count = 1
while @count < @maxcols + 1
begin
--Each column
--This is failing when there is more than one foreign key on a column
select @default_id = cdefault from syscolumns
where id = @table_id and colid = @count
select @default_name = name from sysobjects where id = @default_id
select @default = text from syscomments where id = @default_id
select @iscomputed = iscomputed from syscolumns where id = @table_id and colid = @count
select @seed = IDENT_SEED(name) from sysobjects
where IDENT_SEED(name) IS NOT NULL
and id = @table_id
select @incr = IDENT_INCR(name) from sysobjects
where IDENT_INCR(name) IS NOT NULL
and id = @table_id
if @iscomputed = 0
begin
select @statement = space(13) + '[' + name + '] [' + type_name(xtype) + ']'
+ case
when type_name(xtype) in ('decimal','numeric') then
' ('+ convert(varchar,prec) + ',' + convert(varchar,length) + ')'
+ case when autoval is null then '' else ' IDENTITY(' + @seed + ',' + @incr + ')' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
WHEN type_name(xtype) IN ('float','real') THEN
' ('+ convert(varchar,prec) + ')'
+ case when autoval is null then '' else ' IDENTITY(' + @seed + ',' + @incr + ')' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
--ie varchar(40)
WHEN type_name(xtype) IN ('char','varchar','nchar','nvarchar') THEN
' ('+ convert(varchar,length) + ')'
+ ' COLLATE ' + collation
+ case when autoval is null then '' else ' IDENTITY(' + @seed + ',' + @incr + ')' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
--ie int
ELSE
+ case when autoval is null then '' else ' IDENTITY(' + @seed + ',' + @incr + ')' end
+ CASE when isnullable=0 THEN ' NOT NULL' ELSE ' NULL' END
end
from syscolumns
where colid = @count
and id = @table_id
end
else
begin
select @statement = space(13) + '[' + name + '] AS '
from syscolumns
where colid = @count
and id = @table_id
select @default = text from syscomments where id = @table_id and number = @count
select @statement = @statement + @default + ' ,' + char(13)
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
end
if (len(@default) > 0)
begin
select @statement = @statement + ' CONSTRAINT [' + @default_name + '] DEFAULT ' + @default
select @default = ''
end
if (@count < @maxcols)
begin
select @statement = @statement + ' ,'
end
if @count = @maxcols
begin
select @default_id = constid from sysconstraints where id=@table_id and colid=0
if @default_id <> 0
begin
select @statement = @statement + ' ,'
end
end
select @statement = @statement + char(13)
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
select @count = @count + 1
end
--Do the Primary Keys
--Table constraints
select @default_id = id from sysobjects where parent_obj=@table_id
and xtype = 'PK'
if @default_id <> 0
begin
select @default_name = name from sysobjects where id = @default_id
select @statement = space(13) + 'CONSTRAINT [' + @default_name + '] PRIMARY KEY'
select @clustered = indid from sysindexes
where id = @table_id
and name = @default_name
if @clustered = 1
begin
select @statement = @statement + ' CLUSTERED' + char(13) + space(13) + '(' + char(13)
end
else
if @clustered > 1 and @clustered <> 255
begin
select @statement = @statement + ' NONCLUSTERED' + char(13) + space(13) + '(' + char(13)
end
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
select @indid = indid from sysindexes
where id = @table_id
and name = @default_name
declare cols cursor for
select colid from sysindexkeys
where id = @table_id
and indid = @indid
open cols
fetch next from cols into @count
while @@FETCH_STATUS =0
begin
select @statement = space(24) + '[' + name + ']' from syscolumns
where colid = @count
and id = @table_id
fetch next from cols into @count
if @@FETCH_STATUS =0
begin
select @statement = @statement + ' ,' + char(13)
end
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
end
close cols
deallocate cols
select @statement = char(13) + space(13) + ') ON [PRIMARY]'
select @default_id = 0
select @default_id = id from sysobjects where parent_obj=@table_id
and xtype = 'FK'
if @default_id <> 0
begin
select @statement = @statement + ' ,'
end
select @statement = @statement + char(13)
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
end
--Do the Foreign Keys
--Table constraints
--This needs to be looped as you can have more than one foreign key on a table
select @default_id = 0
declare foreignkeys cursor for
select id from sysobjects
where parent_obj = @table_id
and xtype = 'F'
-- select @table_id
open foreignkeys
fetch next from foreignkeys into @default_id
--This loop needs fixing as the comma's are in the wrong place
--and it doesn't loop correctly for multiple foreign keys
WHILE @@FETCH_STATUS =0
begin
select @default_name = name from sysobjects where id = @default_id
select @statement = space(13) + 'CONSTRAINT [' + @default_name + '] FOREIGN KEY' + char(13) + space(13) + '(' + char(13)
select @clustered = indid from sysindexes
where id = @table_id
and name = @default_name
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
--Need to link it to the columns
declare cols cursor for
select fkey from sysforeignkeys
where fkeyid = @table_id
and constid = @default_id
-- select @table_id
-- select @default_id
open cols
fetch next from cols into @count
while @@FETCH_STATUS =0
begin
select @statement = space(24) + '[' + name + ']' from syscolumns
where colid = @count
and id = @table_id
fetch next from cols into @count
if @@FETCH_STATUS =0
begin
select @statement = @statement + ' ,' + char(13)
end
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
end
close cols
deallocate cols
--References
declare cols cursor for
select rkeyid,rkey from sysforeignkeys
where fkeyid = @table_id
and constid = @default_id
open cols
fetch next from cols into @ftable_id,@count
select @ftable_name = name from sysobjects
where id = @ftable_id
select @statement = char(13) + space(13) + ') REFERENCES [' + @ftable_name + '] (' + char(13)
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
WHILE @@FETCH_STATUS = 0
begin
select @statement = space(24) + '[' + name + ']' from syscolumns
where colid = @count
and id = @ftable_id
fetch next from cols into @ftable_id,@count
if @@FETCH_STATUS =0
begin
select @statement = @statement + ' ,' + char(13)
end
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
fetch next from cols into @ftable_id,@count
end
close cols
deallocate cols
select @statement = char(13) + space(13) + ')'
fetch next from foreignkeys into @default_id
IF @@FETCH_STATUS = 0
begin
select @statement = @statement + ',' + char(13)
end
else
begin
select @statement=@statement + char(13)
end
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
end
close foreignkeys
deallocate foreignkeys
select @statement = char(13) + ') ON [PRIMARY]' + char(13) + 'GO'
UPDATETEXT #sql_statements.string @ptr @txtlen 0 @statement
select @ptr = TEXTPTR(string),
@txtlen = DATALENGTH(string)
from #sql_statements
select string from #sql_statements
drop table #sql_statements
end
else
begin
RAISERROR('The specified table does not exist in the catalog',16,1)
end
end
