为数据库中的所有表/列生成DDL/DML脚本

问题描述：

这主要是我为了学习一些SQL Server概念而做的实验。假设有以下场景：为数据库中的所有表/列生成DDL/DML脚本

我有一个生产数据库，一个开发数据库和一个测试数据库;
开发数据库比测试数据库更新，包含最近开发的几个新表和列;
我想更新的测试数据库以及（与这些新表和列），但宁可不删除并重新创建该数据库（它包含有用的测试数据）

我下面写的脚本针对“开发”数据库执行，以便它将为数据库的每列生成一个具有条件的脚本。该脚本应然后对其他数据库可以用来更新和条件应添加任何列或表的测试数据库尚不具有：

DECLARE @CURRENT_COLUMN nvarchar(100) 
DECLARE @COLUMN_LITERAL nvarchar(100) 
DECLARE @CURRENT_DEFAULT nvarchar(20) 
DECLARE @CURRENT_DATATYPE nvarchar(100) 
DECLARE @CURRENT_SCHEMA nvarchar(100) 
DECLARE @SQLA nvarchar(max) 
DECLARE @SQLB nvarchar(max) 
DECLARE @CURRENT_TABLE nvarchar(100) 
DECLARE @COMPUTED smallint 
SET @COMPUTED = 0 
PRINT ' 
DECLARE @SQL nvarchar(max) 
' 
DECLARE CUR_SCHEMA CURSOR FOR 
SELECT TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES 

OPEN CUR_SCHEMA 

    FETCH NEXT FROM CUR_SCHEMA 
    INTO @CURRENT_SCHEMA 

WHILE @@FETCH_STATUS = 0 
BEGIN 


DECLARE CUR_TAB CURSOR FOR 
SELECT ist.TABLE_NAME from INFORMATION_SCHEMA.TABLES ist 
WHERE ist.TABLE_SCHEMA = @CURRENT_SCHEMA 
AND EXISTS (
     SELECT TOP 1 name 
     FROM sys.tables 
     where name = ist.TABLE_NAME) 
ORDER BY ist.TABLE_NAME 

OPEN CUR_TAB 

    FETCH NEXT FROM CUR_TAB 
    INTO @CURRENT_TABLE 


WHILE @@FETCH_STATUS = 0 
BEGIN 

     PRINT ' 
     IF OBJECT_ID('''[email protected]_TABLE+''') IS NULL 
     BEGIN 
     SET @SQL = '' 
     CREATE TABLE [' + @CURRENT_TABLE +'] (placeholder bit)'' 
     EXEC sp_executesql @SQL 
     END 
     ' 

DECLARE CUR CURSOR FOR 
SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = @CURRENT_TABLE 
AND TABLE_SCHEMA = @CURRENT_SCHEMA 
ORDER BY ORDINAL_POSITION asc 

OPEN CUR 

    FETCH NEXT FROM CUR 
    INTO @CURRENT_COLUMN, @CURRENT_DATATYPE 
    SET @COLUMN_LITERAL = '[' + @CURRENT_COLUMN + ']' 

WHILE @@FETCH_STATUS = 0 
BEGIN 

    SET @SQLB = '' 
    SET @COMPUTED = 0 

    /* Check if column is computed */ 

     IF (SELECT is_computed FROM sys.columns 
     WHERE object_id = OBJECT_ID(@CURRENT_TABLE) 
     AND name = @CURRENT_COLUMN) = 1 
     BEGIN 
     SET @SQLB = @SQLB + 'IF NOT EXISTS(SELECT TOP 1 sc.name FROM sys.columns sc 
     INNER JOIN sys.tables st ON st.object_id = sc.object_id 
     INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
     WHERE sc.Name = ''' + @CURRENT_COLUMN + ''' 
     AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
     AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
BEGIN 
ALTER TABLE ' + @CURRENT_SCHEMA + '.[' + @CURRENT_TABLE + '] 
     ADD ' + @CURRENT_COLUMN + ' AS ' + 
     (SELECT definition FROM sys.computed_columns 
     WHERE object_id = OBJECT_ID(@CURRENT_TABLE) 
     AND name = @CURRENT_COLUMN) 

     SET @COMPUTED = 1 

     END 

     /* Check for identity */ 

     IF (SELECT is_identity FROM sys.columns WHERE object_id = OBJECT_ID(@CURRENT_TABLE) 
     AND name = @CURRENT_COLUMN) = 1 
     BEGIN 
     SET @SQLB = @SQLB + ' IDENTITY (' + 
     CAST((SELECT IDENT_SEED(@CURRENT_SCHEMA + '.[' + @CURRENT_TABLE + ']')) AS VARCHAR(4)) + ',' + 
     CAST((SELECT IDENT_INCR(@CURRENT_SCHEMA + '.[' + @CURRENT_TABLE + ']')) AS VARCHAR(4)) + ')' 
     END 

     /* Check if NULL is allowed */ 

     IF (SELECT sc.is_nullable from sys.columns sc 
     INNER JOIN sys.tables st ON st.object_id = sc.object_id 
     INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
     INNER JOIN sys.types sp ON sp.system_type_id = sc.system_type_id 
     WHERE st.name = @CURRENT_TABLE 
     AND sc.name = @CURRENT_COLUMN 
     AND ss.name = @CURRENT_SCHEMA 
     AND sp.name = @CURRENT_DATATYPE 
    ) = 0 
     BEGIN 
     SET @SQLB = @SQLB + ' NOT NULL' 
     END 
     ELSE SET @SQLB = @SQLB + ' NULL' 

     /* Check for defaults */ 

     IF (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE COLUMN_NAME = @CURRENT_COLUMN 
     AND TABLE_SCHEMA = @CURRENT_SCHEMA 
     AND TABLE_NAME = @CURRENT_TABLE) IS NOT NULL 
     BEGIN 
     SET @CURRENT_DEFAULT = ' DEFAULT ' + (SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE COLUMN_NAME = @CURRENT_COLUMN 
     AND TABLE_SCHEMA = @CURRENT_SCHEMA 
     AND TABLE_NAME = @CURRENT_TABLE) 

     END 
ELSE SET @CURRENT_DEFAULT = '' 


IF @CURRENT_DATATYPE in ('date','datetime2','datetime','time', 
     'smalldatetime','datetimeoffset','text','ntext', 
     'varchar','char','nchar','nvarchar') 

    BEGIN 

     /* Check for date related data types */ 

    IF @CURRENT_DATATYPE in ('date','datetime2','datetime','time', 
     'smalldatetime','datetimeoffset') 
     BEGIN 
     SET @SQLA = ' 
    IF NOT EXISTS(SELECT TOP 1 sc.name FROM sys.columns sc 
    INNER JOIN sys.tables st ON st.object_id = sc.object_id 
    INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
    WHERE sc.Name = ''' + @CURRENT_COLUMN + ''' 
    AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
    AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
     BEGIN 
     ALTER TABLE ' + @CURRENT_SCHEMA + '.['+ @CURRENT_TABLE + '] 
     ADD '[email protected]_LITERAL+'' + ' ' + ''[email protected]_DATATYPE+' '[email protected]_DEFAULT 

     END 

     /* Check for MAX column length */ 

    IF (SELECT sc.max_length FROM sys.columns sc 
INNER JOIN sys.tables st ON st.object_id = sc.object_id 
INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
INNER JOIN sys.types sp ON sp.system_type_id = sc.system_type_id 
WHERE st.name = @CURRENT_TABLE 
AND sc.name = @CURRENT_COLUMN 
AND ss.name = @CURRENT_SCHEMA 
AND sp.name = @CURRENT_DATATYPE) = -1 

BEGIN 

SET @SQLA = ' 
    IF NOT EXISTS(SELECT TOP 1 sc.name FROM sys.columns sc 
    INNER JOIN sys.tables st ON st.object_id = sc.object_id 
    INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
    WHERE sc.Name = ''' + @CURRENT_COLUMN + ''' 
    AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
    AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
     BEGIN 
     ALTER TABLE ' + @CURRENT_SCHEMA + '.['+ @CURRENT_TABLE + '] 
     ADD '[email protected]_LITERAL+'' + ' ' + ''[email protected]_DATATYPE+'(MAX)'+' ' + @CURRENT_DEFAULT 
END 

     /* Check for string data types */ 

ELSE IF @CURRENT_DATATYPE in ('varchar','char','nchar','nvarchar') 
BEGIN 
SET @SQLA = ' 
    IF NOT EXISTS(SELECT TOP 1 sc.name FROM sys.columns sc 
    INNER JOIN sys.tables st ON st.object_id = sc.object_id 
    INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
    WHERE sc.Name = ''' + @CURRENT_COLUMN + ''' 
    AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
    AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
     BEGIN 
     ALTER TABLE ' + @CURRENT_SCHEMA + '.[' + @CURRENT_TABLE + '] 
     ADD '[email protected]_LITERAL+'' + ' ' + ''[email protected]_DATATYPE+'' 
     + '(' + 
     CAST( 
     (SELECT 
     CASE WHEN @CURRENT_DATATYPE IN ('nchar', 'nvarchar') THEN MAX(sc.max_length)/2 
     ELSE MAX(sc.max_length) END AS 'max_length' FROM sys.columns sc 
     INNER JOIN sys.tables st ON st.object_id = sc.object_id 
     INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
     INNER JOIN sys.types sp ON sp.system_type_id = sc.system_type_id 
     WHERE st.name = @CURRENT_TABLE 
     AND sc.name = @CURRENT_COLUMN 
     AND ss.name = @CURRENT_SCHEMA 
     AND sp.name = @CURRENT_DATATYPE 
     ) 
     AS VARCHAR(10)) +')'[email protected]_DEFAULT 

END 

    /* Check for text and ntext types (no column width) */ 

ELSE IF @CURRENT_DATATYPE in ('text','ntext') 
BEGIN 
SET @SQLA = ' 
    IF NOT EXISTS(SELECT TOP 1 sc.name FROM sys.columns sc 
    INNER JOIN sys.tables st ON st.object_id = sc.object_id 
    INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
    WHERE sc.Name = ''' + @CURRENT_COLUMN + ''' 
    AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
    AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
     BEGIN 
     ALTER TABLE ' + @CURRENT_SCHEMA + '.[' + @CURRENT_TABLE + '] 
     ADD '[email protected]_LITERAL+'' + ' ' + ''[email protected]_DATATYPE+' '[email protected]_DEFAULT 

END 


    END 
ELSE 

     /* Check for decimal and numeric types */ 

IF @CURRENT_DATATYPE in ('decimal','numeric') 
    BEGIN 


SET @SQLA = ' 
    IF NOT EXISTS(SELECT TOP 1 sc.name FROM sys.columns sc 
    INNER JOIN sys.tables st ON st.object_id = sc.object_id 
    INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
    WHERE sc.Name = ''' + @CURRENT_COLUMN + ''' 
    AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
    AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
     BEGIN 
     ALTER TABLE ' + @CURRENT_SCHEMA + '.[' + @CURRENT_TABLE + '] 
     ADD '[email protected]_LITERAL+'' + ' ' + ''[email protected]_DATATYPE+''+'(' + CAST((SELECT MIN(NUMERIC_PRECISION) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_NAME = @CURRENT_TABLE 
     AND COLUMN_NAME = @CURRENT_COLUMN 
     AND TABLE_SCHEMA = @CURRENT_SCHEMA 
     AND DATA_TYPE = @CURRENT_DATATYPE 
     ) AS VARCHAR(10)) + ',' + 

     CAST((SELECT MIN(NUMERIC_SCALE) FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_NAME = @CURRENT_TABLE 
     AND COLUMN_NAME = @CURRENT_COLUMN 
     AND DATA_TYPE = @CURRENT_DATATYPE 
     ) AS VARCHAR(10)) + ')'+ @CURRENT_DEFAULT 

    END 
ELSE 
    BEGIN 
SET @SQLA = ' 
    IF NOT EXISTS(SELECT TOP 1 sc.name FROM sys.columns sc 
    INNER JOIN sys.tables st ON st.object_id = sc.object_id 
    INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
    WHERE sc.Name = ''' + @CURRENT_COLUMN + ''' 
    AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
    AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
     BEGIN 
     ALTER TABLE ' + @CURRENT_SCHEMA + '.[' + @CURRENT_TABLE + '] 
     ADD '[email protected]_LITERAL+'' + ' ' + ''[email protected]_DATATYPE+''[email protected]_DEFAULT 


END 

IF @COMPUTED = 0 
BEGIN 

    PRINT @SQLA + @SQLB + ' 
    END 
    ' 


END 

    FETCH NEXT FROM CUR 
    INTO @CURRENT_COLUMN, @CURRENT_DATATYPE 
    SET @COLUMN_LITERAL = '[' + @CURRENT_COLUMN + ']' 

END 
CLOSE CUR; 
DEALLOCATE CUR; 

PRINT ' 
    IF EXISTS 
    (SELECT TOP 1 sc.name FROM sys.columns sc 
    INNER JOIN sys.tables st ON st.object_id = sc.object_id 
    INNER JOIN sys.schemas ss ON ss.schema_id = st.schema_id 
    WHERE sc.Name = ''placeholder'' 
    AND st.Object_ID = OBJECT_ID('''+ @CURRENT_TABLE+ ''') 
    AND ss.name = ''' + @CURRENT_SCHEMA + ''') 
      BEGIN 
      ALTER TABLE '[email protected]_SCHEMA+'.['[email protected]_TABLE+'] DROP COLUMN [placeholder] 
      END 

      ' 

    FETCH NEXT FROM CUR_TAB 
    INTO @CURRENT_TABLE 

END 
CLOSE CUR_TAB 
DEALLOCATE CUR_TAB 

END 
CLOSE CUR_SCHEMA 
DEALLOCATE CUR_SCHEMA

问题：

莫非我避免使用“占位符”列？（我添加了它，因为我不能创建空列以防它们不存在）。
是否接受三个游标的使用？我相信这可以简化，也许与临时表或表变量。
我的方法来捕捉特殊的格式化案例（如数字数据类型定义或最大列字符长度）内聚？
脚本是否完全正确？我对一个真实数据库的副本进行了深入测试，并测试了它针对空白数据库生成的脚本，并且似乎产生了预期的结果。
我使用的变量数量是否过多？我的任何变数都不相关吗？
是否同时使用INFORMATION_SCHEMA和系统表是否可以接受？（我曾经使用过INFORMATION_SCHEMA以避免超出表格连接）。
我正确使用游标吗？
你会提出一个不同的方法来处理我的脚本的某个部分吗？

谢谢，并且很抱歉提出这么多问题。只回答一个或一些，如果你不想回答所有的问题！

** 注 **

我写这一个SQL Server 2008数据库，但是你能指出了新版本的替代品，以帮助改善我的知识
我知道这个脚本没有按” t复制存储过程，触发器和其他东西，但可以在之后使用SSMS自动编写脚本，因此我只在列表中包含列属性。

或者你可以购买的东西，如SQL展鹏从哪个会做所有这一切都为你比较。你发布的代码似乎错过了很多非常重要的东西，比如约束，外键，主键，索引。它在模式方面也有一些严重的问题。如果您在两个模式中具有相同的表名，那么您的代码将全部混淆，因为您没有指定模式。 –

我知道sqlcompare（虽然从来没有购买过它）。我将这个脚本作为一个实验，正如我指出的那样，我并不关心外键，主键和索引等事情，因为SSMS可以编写脚本，我可以在脚本上运行它的脚本。关于模式，第一个游标** CUR_SCHEMA **强制执行每个模式和该模式中每个表的迭代（或者至少应该，需要进一步测试）。 – Renato

不，它不会工作。您的查询“SELECT COLUMN_NAME，DATA_TYPE from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @CURRENT_TABLE ORDER BY ORDINAL_POSITION asc”将从所有具有相同名称的表中返回行，无论它处于哪个模式中。 –

答

所以我建议使用Sql Server数据工具和数据库项目。它允许您导入现有数据库的模式或创建新的数据库项目。您可以将它链接到TFS或Git，您的源代码控制首选项。在这种情况下，您不必拥有单独的SQL脚本来生成模式。如果您决定从SSDT转向模式开发，那么代码库将始终具有最新的已知模式。然后，您可以生成用于部署新代码库的脚本，并且SSDT或Visual Studio将会计算出SQL，我强烈建议在部署到产品之前检查这些脚本。也可以从此工具发布对目标数据库的更改。

SSDT

我忘了提及在属性中你可以指定你的项目的目标是哪个SQL Server版本，因此它可以是2008或2012或2014或2016.然后项目会寻找一个代码，它可以或不可以用在给定的版本的SQL Server。替代这是Redgate –

谢谢。我一定会试一试！然而，你不会推荐基于查询的模式复制，然后呢？ – Renato

@Dariusz除了大于几个TB的表格之外，我会再次提供该解决方案（他们需要脚本，以便获得最佳方法，否则部署需要很长时间） –

为数据库中的所有表/列生成DDL/DML脚本

相关推荐