从SQL Server数据库生成DDL脚本

本文介绍了从SQL Server数据库生成DDL脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如何使用SQL（选择/存储过程/等）从SQL Server数据库生成所有表的DDL（带有外键，索引等）脚本？我需要除数据以外的所有内容。

How to generate DDL (with Foreign keys, indexes, etc.) script of all tables from SQL Server database using SQL (select/stored procedure/etc.)? i need everything except the data.

我不能使用Sql Server Management Studio，因为我想在node.js脚本中使用它将在Linux上运行！

I can't use Sql Server Management Studio because i want to use it in a node.js script that will run over linux!

推荐答案

对于表格：（您可以按以下方式工作）

For tables : (you can work as follows)

DECLARE @C VARCHAR(200) DECLARE C CURSOR FOR SELECT 'EXEC [DBO].[SP_GETDDL] '''+NAME+'''' FROM SYS.TABLES OPEN C FETCH NEXT FROM C INTO @C WHILE @@FETCH_STATUS=0 BEGIN EXEC (@C) FETCH NEXT FROM C INTO @C END CLOSE C DEALLOCATE C

过程如下：

CREATE PROCEDURE [dbo].[Sp_getddl] @TBL VARCHAR(255) AS BEGIN SET nocount ON DECLARE @TBLNAME VARCHAR(200), @SCHEMANAME VARCHAR(255), @STRINGLEN INT, @TABLE_ID INT, @FINALSQL VARCHAR(max), @CONSTRAINTSQLS VARCHAR(max), @CHECKCONSTSQLS VARCHAR(max), @RULESCONSTSQLS VARCHAR(max), @FKSQLS VARCHAR(max), @TRIGGERSTATEMENT VARCHAR(max), @EXTENDEDPROPERTIES VARCHAR(max), @INDEXSQLS VARCHAR(max), @vbCrLf CHAR(2) SET @vbCrLf = Char(13) + Char(10) SELECT @SCHEMANAME = Isnull(Parsename(@TBL, 2), 'dbo'), @TBLNAME = Parsename(@TBL, 1) SELECT @TABLE_ID = [object_id] FROM sys.objects WHERE [type] = 'U' AND [name] <> 'dtproperties' AND [name] = @TBLNAME AND [schema_id] = Schema_id(@SCHEMANAME); IF Isnull(@TABLE_ID, 0) = 0 BEGIN SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + Upper(@TBLNAME) + '] does not exist in Database [' + Db_name() + ']' SELECT @FINALSQL; RETURN 0 END SELECT @FINALSQL = 'CREATE TABLE [' + @SCHEMANAME + '].[' + Upper(@TBLNAME) + '] ( ' SELECT @TABLE_ID = Object_id(@TBLNAME) SELECT @STRINGLEN = Max(Len(sys.columns.[name])) + 1 FROM sys.objects INNER JOIN sys.columns ON sys.objects.[object_id] = sys.columns.[object_id] AND sys.objects.[object_id] = @TABLE_ID; SELECT @FINALSQL = @FINALSQL + CASE WHEN sys.columns.[is_computed] = 1 THEN @vbCrLf + '[' + Upper( sys.columns.[name]) + '] ' + Space(@STRINGLEN - Len(sys.columns.[name])) + 'AS ' + Isnull(CALC.definition, '') ELSE @vbCrLf + '[' + Upper(sys.columns.[name]) + '] ' + Space( @STRINGLEN - Len(sys.columns.[name])) + Upper(Type_name(sys.columns.[user_type_id])) + CASE WHEN Type_name(sys.columns.[user_type_id]) IN ( 'decimal', 'numeric') THEN '(' + CONVERT( VARCHAR, sys.columns.[precision]) + ',' + CONVERT( VARCHAR, sys.columns.[scale]) + ') ' + Space(6 - Len( CONVERT( VARCHAR, sys.columns.[precision]) + ',' + CONVERT(VARCHAR, sys.columns.[scale]))) + Space( 2) -- + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id]))) + CASE WHEN Columnproperty ( @TABLE_ID, sys.columns.[name], 'IsIdentity' ) = 0 THEN ' ' ELSE ' IDENTITY(' + CONVERT(VARCHAR, Isnull(Ident_seed(@TBLNAME), 1) ) + ',' + CONVERT(VARCHAR, Isnull(Ident_incr(@TBLNAME), 1) ) + ')' END + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END WHEN Type_name(sys.columns.[user_type_id]) IN ( 'float', 'real') THEN CASE WHEN sys.columns.[precision] = 53 THEN Space(11 - Len(CONVERT(VARCHAR, sys.columns.[precision]))) + Space(7) + Space(16 - Len(Type_name(sys.columns.[user_type_id]))) + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END ELSE '(' + CONVERT( VARCHAR, sys.columns.[precision]) + ') ' + Space(6 - Len( CONVERT( VARCHAR, sys.columns.[precision]))) + Space(7) + Space (16 - Len(Type_name(sys.columns.[user_type_id]))) + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END END WHEN Type_name(sys.columns.[user_type_id]) IN ( 'char', 'varchar') THEN CASE WHEN sys.columns.[max_length] = -1 THEN '(max)' + Space(6 - Len(CONVERT(VARCHAR, sys.columns.[max_length]))) + Space(7) + Space(16 - Len(Type_name(sys.columns.[user_type_id]))) + CASE WHEN sys.columns.collation_name IS NULL THEN '' ELSE ' COLLATE ' + sys.columns.collation_name END + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END ELSE '(' + CONVERT(VARCHAR, sys.columns.[max_length]) + ') ' + Space(6 - Len(CONVERT(VARCHAR, sys.columns.[max_length]))) + Space(7) + Space(16 - Len(Type_name(sys.columns.[user_type_id]))) + CASE WHEN sys.columns.collation_name IS NULL THEN '' ELSE ' COLLATE ' + sys.columns.collation_name END + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END END WHEN Type_name(sys.columns.[user_type_id]) IN ( 'nchar', 'nvarchar' ) THEN CASE WHEN sys.columns.[max_length] = -1 THEN '(max)' + Space (6 - Len(CONVERT(VARCHAR, (sys.columns.[max_length])))) + Space(7) + Space(16 - Len(Type_name( sys.columns.[user_type_id]))) + CASE WHEN sys.columns.collation_name IS NULL THEN '' ELSE ' COLLATE ' + sys.columns.collation_name END + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END ELSE '(' + CONVERT( VARCHAR, (sys.columns.[max_length])) + ') ' + Space(6 - Len ( CONVERT( VARCHAR, (sys.columns.[max_length])))) + Space(7) + Space(16 - Len(Type_name(sys.columns.[user_type_id]))) + CASE WHEN sys.columns.collation_name IS NOT NULL THEN '' ELSE ' COLLATE ' + sys.columns.collation_name END + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END END WHEN Type_name( sys.columns.[user_type_id]) IN ('datetime', 'money', 'text', 'image') THEN Space(18 - Len(Type_name(sys.columns.[user_type_id]))) + ' ' + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END ELSE Space(16 - Len(Type_name(sys.columns.[user_type_id]))) + CASE WHEN Columnproperty ( @TABLE_ID, sys.columns.[name], 'IsIdentity' ) = 0 THEN ' ' ELSE ' IDENTITY(' + CONVERT( VARCHAR, Isnull(Ident_seed(@TBLNAME), 1) ) + ',' + CONVERT( VARCHAR, Isnull(Ident_incr(@TBLNAME), 1) ) + ')' END + Space( 2) + CASE WHEN sys.columns.[is_nullable] = 0 THEN ' NOT NULL' ELSE ' NULL' END END + CASE WHEN sys.columns.[default_object_id] = 0 THEN '' --ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'') --optional section in case NAMED default cosntraints are needed: ELSE @vbCrLf + 'CONSTRAINT [' + def.NAME + '] DEFAULT ' + Isnull(def.[definition], '') --i thought it needed to be handled differently! NOT! END --CASE cdefault END --iscomputed + ',' FROM sys.columns LEFT OUTER JOIN sys.default_constraints DEF ON sys.columns.[default_object_id] = DEF.[object_id] LEFT OUTER JOIN sysputed_columns CALC ON sys.columns.[object_id] = CALC.[object_id] AND sys.columns.[column_id] = CALC.[column_id] WHERE sys.columns.[object_id] = @TABLE_ID ORDER BY sys.columns.[column_id] SELECT @STRINGLEN = Max(Len([name])) + 1 FROM sys.objects DECLARE @Results TABLE ( [schema_id] INT, [schema_name] VARCHAR(255), [object_id] INT, [object_name] VARCHAR(255), [index_id] INT, [index_name] VARCHAR(255), [rows] INT, [sizemb] DECIMAL(19, 3), [indexdepth] INT, [type] INT, [type_desc] VARCHAR(30), [fill_factor] INT, [is_unique] INT, [is_primary_key] INT, [is_unique_constraint] INT, [index_columns_key] VARCHAR(max), [index_columns_include] VARCHAR(max) ) INSERT INTO @Results SELECT sys.schemas.schema_id, sys.schemas.[name] AS schema_name, sys.objects.[object_id], sys.objects.[name] AS object_name, sys.indexes.index_id, Isnull(sys.indexes.[name], '---') AS index_name, partitions.rows, partitions.sizemb, Indexproperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth, sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor, sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint, Isnull(Index_Columns.index_columns_key, '---') AS index_columns_key, Isnull(Index_Columns.index_columns_include, '---') AS index_columns_include FROM sys.objects JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id JOIN sys.indexes ON sys.objects.[object_id] = sys.indexes.[object_id] JOIN (SELECT [object_id], index_id, Sum(row_count) AS Rows, CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), Sum( in_row_reserved_page_count + lob_reserved_page_count + row_overflow_reserved_page_count )) / CONVERT(NUMERIC(19, 3), 128)) AS SizeMB FROM sys.dm_db_partition_stats GROUP BY [object_id], index_id) AS partitions ON sys.indexes.[object_id] = partitions.[object_id] AND sys.indexes.index_id = partitions.index_id CROSS apply (SELECT LEFT(index_columns_key, Len(index_columns_key) - 1) AS index_columns_key , LEFT(index_columns_include, Len(index_columns_include) - 1) AS index_columns_include FROM (SELECT (SELECT sys.columns.[name] + ',' + ' ' FROM sys.index_columns JOIN sys.columns ON sys.index_columns.column_id = sys.columns.column_id AND sys.index_columns.[object_id] = sys.columns.[object_id] WHERE sys.index_columns.is_included_column = 0 AND sys.indexes.[object_id] = sys.index_columns.[object_id] AND sys.indexes.index_id = sys.index_columns.index_id ORDER BY key_ordinal FOR xml path('')) AS index_columns_key, (SELECT sys.columns.[name] + ',' + ' ' FROM sys.index_columns JOIN sys.columns ON sys.index_columns.column_id = sys.columns.column_id AND sys.index_columns.[object_id] = sys.columns.[object_id] WHERE sys.index_columns.is_included_column = 1 AND sys.indexes.[object_id] = sys.index_columns.[object_id] AND sys.indexes.index_id = sys.index_columns.index_id ORDER BY index_column_id FOR xml path('')) AS index_columns_include) AS Index_Columns) AS Index_Columns WHERE sys.schemas.[name] LIKE CASE WHEN @SCHEMANAME = '' THEN sys.schemas.[name] ELSE @SCHEMANAME END AND sys.objects.[name] LIKE CASE WHEN @TBLNAME = '' THEN sys.objects.[name] ELSE @TBLNAME END ORDER BY sys.schemas.[name], sys.objects.[name], sys.indexes.[name] --@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results: SET @CONSTRAINTSQLS = '' SET @INDEXSQLS = '' SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS + CASE WHEN is_primary_key = 1 OR is_unique = 1 THEN @vbCrLf + 'CONSTRAINT [' + index_name + '] ' + Space ( @STRINGLEN - Len(index_name)) + CASE WHEN is_primary_key = 1 THEN ' PRIMARY KEY ' ELSE CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END END + type_desc + CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE ' ' END + ' (' + index_columns_key + ')' + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT( VARCHAR(30), fill_factor) ELSE '' END ELSE '' END + ',' FROM @RESULTS WHERE [type_desc] != 'HEAP' AND is_primary_key = 1 OR is_unique = 1 ORDER BY is_primary_key DESC, is_unique DESC SELECT @INDEXSQLS = @INDEXSQLS + CASE WHEN is_primary_key = 0 OR is_unique = 0 THEN @vbCrLf + 'CREATE INDEX [' + index_name + '] ' + Space( @STRINGLEN - Len(index_name)) + ' ON [' + [object_name] + ']' + ' (' + index_columns_key + ')' + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30), fill_factor) ELSE '' END END FROM @RESULTS WHERE [type_desc] != 'HEAP' AND is_primary_key = 0 AND is_unique = 0 ORDER BY is_primary_key DESC, is_unique DESC IF @INDEXSQLS <> '' SET @INDEXSQLS = @vbCrLf + 'GO' + @vbCrLf + @INDEXSQLS SET @CHECKCONSTSQLS = '' SELECT @CHECKCONSTSQLS = @CHECKCONSTSQLS + @vbCrLf + Isnull('CONSTRAINT [' + sys.objects.[name] + '] ' + Space( @STRINGLEN - Len(sys.objects.[name])) + ' CHECK ' + Isnull(sys.check_constraints.definition, '') + ',', '') FROM sys.objects INNER JOIN sys.check_constraints ON sys.objects.[object_id] = sys.check_constraints.[object_id] WHERE sys.objects.type = 'C' AND sys.objects.parent_object_id = @TABLE_ID SET @FKSQLS = ''; SELECT @FKSQLS = @FKSQLS + @vbCrLf + 'CONSTRAINT [' + Object_name(constid) + ']' + Space(@STRINGLEN - Len(Object_name(constid) )) + ' FOREIGN KEY (' + Col_name(fkeyid, fkey) + ') REFERENCES ' + Object_name(rkeyid) + '(' + Col_name(rkeyid, rkey) + '),' FROM sysforeignkeys WHERE fkeyid = @TABLE_ID SET @RULESCONSTSQLS = '' SELECT @RULESCONSTSQLS = @RULESCONSTSQLS + Isnull( @vbCrLf + 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' + CONVERT(VARCHAR(30), sys.objects.schema_id) + ' AND [name] = ''[' + Object_name(sys.columns.[rule_object_id]) + ']'')' + @vbCrLf + sys.sql_modules.definition + @vbCrLf + 'GO' + @vbCrLf + 'EXEC sp_binderule [' + sys.objects.[name] + '], ''[' + Object_name(sys.columns.[object_id]) + '].[' + sys.columns.[name] + ']''' + @vbCrLf + 'GO', '') FROM sys.columns INNER JOIN sys.objects ON sys.objects.[object_id] = sys.columns.[object_id] INNER JOIN sys.sql_modules ON sys.columns.[rule_object_id] = sys.sql_modules.[object_id] WHERE sys.columns.[rule_object_id] <> 0 AND sys.columns.[object_id] = @TABLE_ID SET @TRIGGERSTATEMENT = '' SELECT @TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + sys.sql_modules.[definition] + @vbCrLf + 'GO' FROM sys.sql_modules WHERE [object_id] IN(SELECT [object_id] FROM sys.objects WHERE type = 'TR' AND [parent_object_id] = @TABLE_ID) IF @TRIGGERSTATEMENT <> '' SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT SET @EXTENDEDPROPERTIES = '' SELECT @EXTENDEDPROPERTIES = @EXTENDEDPROPERTIES + @vbCrLf + 'EXEC sys.sp_addextendedproperty @name = N''' + [name] + ''', @value = N''' + Replace(CONVERT(VARCHAR(max), [value]), '''', '''''') + ''', @level0type = N''SCHEMA'', @level0name = [' + @SCHEMANAME + '], @level1type = N''TABLE'', @level1name = [' + @TBLNAME + '];' FROM Fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, NULL, NULL); IF @EXTENDEDPROPERTIES <> '' SET @EXTENDEDPROPERTIES = @vbCrLf + 'GO' + @vbCrLf + @EXTENDEDPROPERTIES SELECT @FINALSQL = @FINALSQL + @CONSTRAINTSQLS + @CHECKCONSTSQLS + @FKSQLS SET @FINALSQL = Substring(@FINALSQL, 1, Len(@FINALSQL) - 1); SET @FINALSQL = @FINALSQL + ')' + @vbCrLf; SELECT @FINALSQL + @INDEXSQLS + @RULESCONSTSQLS + @TRIGGERSTATEMENT + @EXTENDEDPROPERTIES END go