生成SQL Server表脚本的脚本

Question

我有以下情况：我正在从事一个数据量庞大的项目，其中包含数百张表，经常情况下我知道我需要为哪张表生成脚本，但在所有表的列表中查找它需要很长时间。我想知道是否有一种编写脚本以生成表格脚本的方法。由于我已经知道表名，所以只需输入表名比在巨大的相似名称列表中搜索更容易。

- Misha Huziuk

---

RedGate有一个免费的SQL搜索工具，可以让您搜索并在您从搜索结果中选择对象时提取数据库对象定义。http://www.red-gate.com/products/sql-development/sql-search/ - mallan1121

如果你真的需要的话，你可以在对象资源管理器中使用过滤功能。只需右键点击“Tables”，然后选择“Filter -> Filter Settings”。顺便说一下，我理解你的痛苦。我有一个非常难以处理的系统。有超过4,000个表格，它们的名称是从一个老式的RPG系统开始的，所以每个表格的名称都恰好有6个字符，并且都是大写字母。 - Sean Lange

请参阅DBA Stack Exchange网站上的此问题/答案：http://dba.stackexchange.com/questions/53085/is-there-a-way-to-generate-table-create-script-in-tsql - Joe Stefanelli

Answer 1

是的，你绝对可以编写自己的过程来为给定的表名生成脚本，该表名将作为参数提供。在我的示例中，我将“dbo.TABLE1”作为参数传递给此查询：

（当然，您可以将其包装到存储过程中）

DECLARE  
  @object_name SYSNAME  = 'dbo.TABLE1'
, @object_id INT  = object_id('dbo.TABLE1')
, @SQL NVARCHAR(MAX)  

SELECT  
  @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' +        OBJECT_NAME([object_id]) + ']'  
, @object_id = [object_id]  
FROM (SELECT [object_id] = @object_id) o  



SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) +       STUFF((  
SELECT CHAR(13) + '    , [' + c.name + '] ' +   
    CASE WHEN c.is_computed = 1  
        THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)  
        ELSE   
            CASE WHEN c.system_type_id != c.user_type_id   
                THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name +     ']'   
                ELSE '[' + UPPER(tp.name) + ']'   
            END  +   
            CASE   
                WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')  
                    THEN '(' + CASE WHEN c.max_length = -1   
                                    THEN 'MAX'   
                                    ELSE CAST(c.max_length AS VARCHAR(5))   
                                END + ')'  
                WHEN tp.name IN ('nvarchar', 'nchar')  
                    THEN '(' + CASE WHEN c.max_length = -1   
                                    THEN 'MAX'   
                                    ELSE CAST(c.max_length / 2 AS VARCHAR(5))   
                                END + ')'  
                WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')   
                    THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'  
                WHEN tp.name = 'decimal'  
                    THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'  
                ELSE ''  
            END +  
            CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id   
                THEN ' COLLATE ' + c.collation_name  
                ELSE ''  
            END +  
            CASE WHEN c.is_nullable = 1   
                THEN ' NULL'  
                ELSE ' NOT NULL'  
            END +  
            CASE WHEN c.default_object_id != 0   
                THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +   
                     ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)  
                ELSE ''  
            END +   
            CASE WHEN cc.[object_id] IS NOT NULL   
                THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]  
                ELSE ''  
            END +  
            CASE WHEN c.is_identity = 1   
                THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +   
                                CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'   
                ELSE ''   
            END   
    END  
FROM sys.columns c WITH(NOLOCK)  
JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id  
LEFT JOIN sys.check_constraints cc WITH(NOLOCK)   
     ON c.[object_id] = cc.parent_object_id   
    AND cc.parent_column_id = c.column_id  
WHERE c.[object_id] = @object_id  
ORDER BY c.column_id  
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, '      ') +   
ISNULL((SELECT '  
, CONSTRAINT [' + i.name + '] PRIMARY KEY ' +   
CASE WHEN i.index_id = 1   
    THEN 'CLUSTERED'   
    ELSE 'NONCLUSTERED'   
END +' (' + (  
SELECT STUFF(CAST((  
    SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +  
            CASE WHEN ic.is_descending_key = 1  
                THEN ' DESC'  
                ELSE ''  
            END  
    FROM sys.index_columns ic WITH(NOLOCK)  
    WHERE i.[object_id] = ic.[object_id]  
        AND i.index_id = ic.index_id  
    FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'  
FROM sys.indexes i WITH(NOLOCK)  
WHERE i.[object_id] = @object_id  
    AND i.is_primary_key = 1), '') + CHAR(13) + ');'  

SELECT   @SQL  

- Rigerta

Answer 2

这里有一个存储过程代码的链接，你只需要提供表名就可以添加并执行以获取DDL。

http://www.stormrage.com/SQLStuff/sp_GetDDLa_Latest.txt

- puzeledbysql