USP_TableScript

Procedure to generate script for an existing table ;

CREATE PROCEDURE USP_TableScript
@TableName varchar(100),
@NewTableName varchar(100),
@RetainNulls int,
@TableDef varchar(max) OUTPUT
AS          
/*
USP_TableScript
Paramters : 'originaltable' , 'newtablename', 0/1 - retain null settings, output variable
*/

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName)          
Begin          
declare @sql varchar(8000)          
declare @table varchar(100)          
declare @cols table (datatype varchar(50))        
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 (@RetainNulls = 1 AND Is_Nullable='NO') then ' NOT NULL,' + CHAR(10) else ' NULL,' + CHAR(10) end             from INFORMATION_SCHEMA.COLUMNS where Table_Name=@tableName               select @table=  'CREATE TABLE [' + @NewTableName + '] ' from INFORMATION_SCHEMA.COLUMNS where table_Name=@tableName             select @sql=@table + substring(@sql,1,len(@sql)-2) +' )'             select @TableDef = replace(@sql,'()','')          End    go 

Usage ;

DECLARE @SQLSTRING NVARCHAR(MAX)
EXEC USP_TableScript 'Contact','##temp_Contact', 0,@SQLSTRING OUTPUT
EXEC sp_executesql @SQLSTRING