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

Rate

Share

Share

Rate