如何在SQL Server中为表生成create / alter脚本，仅使用SQL / t-SQL脚本 [英] How to generate create/alter script for a table in SQL server, only with SQL/t-SQL scripting

问题描述

我想在sql脚本的帮助下获取表的Create / Alter脚本。我不希望它来自对象浏览器 - >点击....依此类推。因为我想要来自c＃应用程序的脚本



我尝试过：



Google，但仅在sql脚本的帮助下没有成功。所有都显示对象资源管理器/ GUI

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

推荐答案

根据我的需要简化版本

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)+' )'

这篇关于如何在SQL Server中为表生成create / alter脚本，仅使用SQL / t-SQL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！