Click here to Skip to main content
14,838,196 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to fetch Create/Alter script for a table, with the help of sql scripting only. I don't want it from object explorer -> click.... and so on. coz I want that script from c# application

What I have tried:

Google, but no success for this with the help of sql script only. All are showing with object explorer/GUI
Posted
Updated 22-Dec-16 3:07am
Comments
Tomas Takac 21-Dec-16 6:32am
   
I guess you need to google harder. Look what I found within few seconds: sql server - Generate SQL Create Scripts for existing tables with Query - Stack Overflow[^]
StackQ 22-Dec-16 7:11am
   
Thanks alot

1 solution

Simplified version according to my need
DECLARE @table_name SYSNAME
SELECT @table_name = REPLACE(REPLACE('dbo.MyTableName','[',''),']','')
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = 
'CREATE TABLE ' + SUBSTRING(@table_name,CHARINDEX('.',@table_name)+1,len(@table_name)) + CHAR(13) + 
'(' + CHAR(13)+
(SELECT CHAR(9)+
ISC.COLUMN_NAME+' '+
CASE 
	WHEN ISC.DATA_TYPE IN ('varchar', 'char', 'varbinary', 'binary', 'text', 'nvarchar', 'nchar', 'ntext')
	THEN UPPER(ISC.DATA_TYPE)+'('+
		CASE 
			WHEN ISC.CHARACTER_MAXIMUM_LENGTH=-1 
			THEN 'MAX' 
			ELSE CAST(ISC.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(6)) 
		END+')'
	
	WHEN ISC.DATA_TYPE LIKE '%DATE%'
	THEN 'DATETIME'
END + CHAR(32)+
CASE IS_NULLABLE
	WHEN 'NO' THEN 'NOT NULL'
	ELSE 'NULL'
END+','+CHAR(13)
FROM INFORMATION_SCHEMA.columns ISC
WHERE 
TABLE_NAME=SUBSTRING(@table_name,CHARINDEX('.',@table_name)+1,len(@table_name)) AND 
TABLE_SCHEMA=SUBSTRING(@table_name,1,CHARINDEX('.',@table_name)-1)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT SUBSTRING(@SQL,1,LEN(@SQL)-2)+CHAR(13)+' )'
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900