get CREATE TABLE script for existing table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get CREATE TABLE script for existing table

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-24 : 11:57:33

Hi,

I want a procedure which will return me the script of 'CREATE TABLE..' of an existing table but with the new table name, which will be passed as one of the input parameter.

So my stored procedure will be taking two parameters:
1 - existing table name
2 - new table name
and it should return/output the 'CREATE TABLE..' script

Thanks in advance
Umer

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-24 : 12:01:01

I came accross this forum for SQL 2000 and I am trying to make the code dynamic but i am doing some silly mistake which is not letting me do it.

Original code:


declare @sql  varchar(4000)
DECLARE @tableName SYSNAME
declare @tname sysname
SET @tableName = 'TestTable'
set @tname = N'Calving_Error'
select 
	case ordinal_position when 1 then 'create table ' + 'dfdfd' + char(10) + '(' + char(10) + char(9) else char(9) + ',' end
	+ column_name
	+ ' '
	+ data_type
	+ case 	when data_type like('%char')
			then '(' + ltrim(character_maximum_length) + ')'
		when data_type in('numeric','decimal')
			then '(' + ltrim(numeric_precision) + ',' + ltrim(numeric_scale) + ')'
		else ''end
	+ case IS_NULLABLE when 'no' then ' NOT NULL' else ' NULL' end
from
	information_schema.columns
where
	table_name = @tname
union all select ')'

Attempt of making it dynamic:


declare @sql  varchar(4000)
DECLARE @tableName SYSNAME
declare @tname sysname
SET @tableName = 'TestTable'
set @tname = N'Calving_Error'
SELECT @sql =	'
					SELECT	CASE ordinal_position 
								WHEN 
									1 
								THEN 
									''CREATE TABLE '' + ' + @tableName +' + CHAR(10) + ''('' + CHAR(10) + CHAR(9) ELSE char(9) + '','' END
										+ column_name
										+ ' '
										+ data_type
										+ CASE 	
											WHEN 
												data_type LIKE(''%char'')
											THEN
												''('' 
													+ LTRIM(character_maximum_length) + 
												'')''
											WHEN 
												data_type IN(''numeric'',''decimal'')
											THEN 
												''('' + LTRIM(numeric_precision) + '','' + LTRIM(numeric_scale) + '')''
											ELSE 
												''
										END
										+ CASE IS_NULLABLE 
											WHEN 
												''no'' 
											THEN 
												'' NOT NULL'' 
											ELSE 
												'' NULL'' 
										END
					FROM	information_schema.columns
					WHERE	table_name = ' + @tname +' UNION ALL SELECT '')''
				'
print @sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 13:08:47

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/generate-sql-script.aspx

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-25 : 04:56:08

That's exactly what I wanted.
Just to make the code clearer I have done it as follow which will use new table name:


CREATEPROCEDURE [dbo].[uspGetCreateTableScript]
	@existingTableName	SYSNAME,	-- Input parameter which will be taking in the existing table name
	@newTableName		SYSNAME		-- chaning the table name to this one in the final create table script

AS

SET NOCOUNT ON
/* DECLARATIONS */
DECLARE @sql VARCHAR(8000)						-- Variable will return either with the CREATE TABLE.. script or error message
DECLARE @table VARCHAR(100)            
declare @cols TABLE (datatype VARCHAR(50))  

IF EXISTS (SELECT * FROM Information_Schema.COLUMNS WHERE Table_Name = @existingTableName)           
BEGIN            
        
	INSERT INTO @cols VALUES('bit')          
	INSERT INTO @cols VALUES('binary')          
	INSERT INTO @cols VALUES('bigint')          
	INSERT INTO @cols VALUES('int')          
	INSERT INTO @cols VALUES('float')          
	INSERT INTO @cols VALUES('datetime')          
	INSERT INTO @cols VALUES('text')          
	INSERT INTO @cols VALUES('image')          
	INSERT INTO @cols VALUES('uniqueidentifier')          
	INSERT INTO @cols VALUES('smalldatetime')          
	INSERT INTO @cols VALUES('tinyint')          
	INSERT INTO @cols VALUES('smallint')          
	INSERT INTO @cols VALUES('sql_variant')          
	           
	SET @sql=''            
	SELECT @sql=@sql+             
	CASE WHEN charindex('(',@sql,1)<=0 THEN '(' ELSE '' END +Column_Name + ' ' +Data_Type +             
	CASE WHEN Data_Type IN (SELECT datatype FROM @cols) THEN '' ELSE  '(' END+
	CASE WHEN data_type IN ('real','money','decimal','numeric')  THEN cast(isnull(numeric_precision,'') AS VARCHAR)+','+
	CASE WHEN data_type IN ('real','money','decimal','numeric') THEN cast(isnull(Numeric_Scale,'') AS VARCHAR) END
	WHEN data_type IN ('char','nvarchar','VARCHAR','nchar') THEN cast(isnull(Character_Maximum_Length,'') AS VARCHAR)       ELSE '' END+
	CASE WHEN Data_Type IN (SELECT datatype FROM @cols)THEN '' ELSE  ')' END+
	CASE WHEN Is_Nullable='No' THEN ' Not null,' ELSE ' null,' END           
	FROM Information_Schema.COLUMNS WHERE Table_Name = @existingTableName            
	 
	  
	SELECT  @table = 'CREATE TABLE ' + @newTableName FROM Information_Schema.COLUMNS WHERE table_Name = @existingTableName
	SELECT @sql = @table + SUBSTRING(@sql,1, LEN(@sql)-1) +' )' 
	--SELECT @sql AS DDL
	RETURN @sql
  
END           

ELSE
BEGIN        
	SET @sql = 'The table ' + @existingTableName + ' does not exist.'    
	RETURN @sql
END

Now the only problem is that it gives me the following error when I am assigning it to @sql.


EXEC uspGetCreateTableScript 'Calving_Error','TestnewTable'

Msg 245, Level 16, State 1, Procedure uspGetCreateTableScript, Line 80
Conversion failed when converting the varchar value 'CREATE TABLE TestnewTable(Freeze_Brand int null,Ear_Tag varchar(10) null,Lact_No smallint null,Calving_Date smalldatetime null,Calving_Wgt int null,Calving_Cond_Score float null,Calving_Ease char(10) null,No_of_Calves smallint null,No_of_Calves_Dead smallint null,Ear_Tag_Calf varchar(10) null,Sex varchar(5) null,Birth_Wgt float null,Birth_Rank smallint null,Recorder int null,Error_Codes int null,Error_Descriptions nvarchar(1000) null )' to data type int.

quote:
Originally posted by visakh16

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/generate-sql-script.aspx

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-25 : 05:04:04

Silly mistake I have done up there....Return only gives out INTEGER values.....is there any other way I can get that VARCHAR variable output/returned from my procedure?

umertahir
Posting Yak Master

154 Posts

Posted - 2009-02-25 : 05:26:52

Ok finally I got it right. The procedure is:


CREATEPROCEDURE [dbo].[uspGetCreateTableScript]
	@existingTableName	SYSNAME,				-- Input parameter which will be taking in the existing table name
	@newTableName		SYSNAME,				-- chaning the table name to this one in the final create table script
	@scriptOutput		VARCHAR(8000) OUTPUT	-- this variable will have the final CREATE TABLE ..script in it which can be used in other procedures
AS
SET NOCOUNT ON
/* DECLARATIONS */
DECLARE @sql VARCHAR(8000)						-- Variable will return either with the CREATE TABLE.. script or error message
DECLARE @table VARCHAR(100)            
declare @cols TABLE (datatype VARCHAR(50))  

IF EXISTS (SELECT * FROM Information_Schema.COLUMNS WHERE Table_Name = @existingTableName)           
BEGIN            
        
	INSERT INTO @cols VALUES('bit')          
	INSERT INTO @cols VALUES('binary')          
	INSERT INTO @cols VALUES('bigint')          
	INSERT INTO @cols VALUES('int')          
	INSERT INTO @cols VALUES('float')          
	INSERT INTO @cols VALUES('datetime')          
	INSERT INTO @cols VALUES('text')          
	INSERT INTO @cols VALUES('image')          
	INSERT INTO @cols VALUES('uniqueidentifier')          
	INSERT INTO @cols VALUES('smalldatetime')          
	INSERT INTO @cols VALUES('tinyint')          
	INSERT INTO @cols VALUES('smallint')          
	INSERT INTO @cols VALUES('sql_variant')          
	           
	SET @sql=''            
	SELECT @sql=@sql+             
	CASE WHEN charindex('(',@sql,1)<=0 THEN '(' ELSE '' END +Column_Name + ' ' +Data_Type +             
	CASE WHEN Data_Type IN (SELECT datatype FROM @cols) THEN '' ELSE  '(' END+
	CASE WHEN data_type IN ('real','money','decimal','numeric')  THEN cast(isnull(numeric_precision,'') AS VARCHAR)+','+
	CASE WHEN data_type IN ('real','money','decimal','numeric') THEN cast(isnull(Numeric_Scale,'') AS VARCHAR) END
	WHEN data_type IN ('char','nvarchar','VARCHAR','nchar') THEN cast(isnull(Character_Maximum_Length,'') AS VARCHAR)       ELSE '' END+
	CASE WHEN Data_Type IN (SELECT datatype FROM @cols)THEN '' ELSE  ')' END+
	CASE WHEN Is_Nullable='No' THEN ' Not null,' ELSE ' null,' END           
	FROM Information_Schema.COLUMNS WHERE Table_Name = @existingTableName            
	 
	  
	SELECT  @table = 'CREATE TABLE ' + @newTableName FROM Information_Schema.COLUMNS WHERE table_Name = @existingTableName
	SELECT @sql = @table + SUBSTRING (@sql, 1, LEN(@sql)-1) +' )'  -- Adding the ending bracket in to the code and assigning it to the return variable
	--SELECT @sql AS DDL
	--RETURN @sql
	SET @scriptOutput = @sql
  
END           

ELSE
BEGIN        
	SET @sql = 'The table ' + @existingTableName + ' does not exist.'    
	SET @scriptOutput = @sql
END

The way to check its working is:


DECLARE @outputvar VARCHAR(8000)
EXEC uspGetCreateTableScript 'Calving_Error','TestnewTable' , @outputvar OUTPUT
PRINT @outputvar

Result:
CREATE TABLE TestnewTable(Freeze_Brand int null,Ear_Tag varchar(10) null,Lact_No smallint null,Calving_Date smalldatetime null,Calving_Wgt int null,Calving_Cond_Score float null,Calving_Ease char(10) null,No_of_Calves smallint null,No_of_Calves_Dead smallint null,Ear_Tag_Calf varchar(10) null,Sex varchar(5) null,Birth_Wgt float null,Birth_Rank smallint null,Recorder int null,Error_Codes int null,Error_Descriptions nvarchar(1000) null )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources