Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Generate SQL Script

Aug 27 2007 9:30AM by Madhivanan   

This procedure will generate the script of a table

Create Procedure GenerateScript 
(            
	@tableName varchar(100)
)            
as            
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 Is_Nullable='No' then ' Not null,' else ' null,' end            
	from 
		Information_Schema.COLUMNS where Table_Name=@tableName            
	 
	  
	select  
		@table=  'Create table ' + table_Name 
	from 
		Information_Schema.COLUMNS 
	where 
		table_Name=@tableName            

	select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'            

	select @sql  as DDL         
	  
End            

Else        
	Select 'The table '+@tableName + ' does not exist'           

Tags: t-sql, sql_server, script,


Madhivanan
3 · 40% · 12924
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • Hi Madhivanan,

    Thanks for posting the solution.

    I ran this script. A small suggestion for this script. If the table have identity column then it does not create script with identity column,

    Regards,
    Rohit
    http://xpode.com

    commented on Sep 10 2011 12:38AM
    rohit
    3063 · 0% · 2
  • Yes it gives only column names with datatypes. I will try to write another post to have full information

    commented on Oct 5 2012 9:17AM
    Madhivanan
    3 · 40% · 12924

Your Comment


Sign Up or Login to post a comment.

"Generate SQL Script" rated 5 out of 5 by 1 readers
Generate SQL Script , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]