sqlserver通过语句 根据表生成建表语句及索引和约束的语句

在 SQL Server 中，可以使用以下语句生成表的建表语句： ``` SELECT 'CREATE TABLE ' + schema_name(tab.schema_id) + '.' + tab.name + '(' + column_list + CASE WHEN EXISTS (SELECT * FROM sys.indexes WHERE object_id = tab.object_id AND is_primary_key = 1) THEN ', CONSTRAINT ' + pk.name + ' PRIMARY KEY CLUSTERED (' + pk_col_list + ')' ELSE '' END + ')' + CASE WHEN tab.create_date <> tab.modify_date THEN '/* Created: ' + CONVERT(NVARCHAR(30),tab.create_date,120) + ', Modified: ' + CONVERT(NVARCHAR(30),tab.modify_date,120) + ' */' ELSE '' END AS create_table_script FROM sys.tables AS tab CROSS APPLY (SELECT STUFF((SELECT ', ' + col.name + ' ' + sys.types.name + CASE WHEN col.is_nullable = 0 THEN ' NOT NULL' ELSE '' END + CASE WHEN defn.definition IS NOT NULL THEN ' DEFAULT' + defn.definition ELSE '' END FROM sys.columns AS col JOIN sys.types ON col.user_type_id = sys.types.user_type_id LEFT JOIN sys.default_constraints AS defn ON col.default_object_id = defn.object_id WHERE col.object_id = tab.object_id ORDER BY col.column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '')) AS column_list LEFT JOIN ( SELECT i.object_id, name, STUFF((SELECT ', ' + col.name FROM sys.index_columns AS ic JOIN sys.columns AS col ON ic.column_id = col.column_id AND ic.object_id = col.object_id WHERE i.object_id = ic.object_id AND i.index_id = ic.index_id ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '') AS pk_col_list FROM sys.indexes AS i WHERE is_primary_key = 1 ) AS pk ON tab.object_id = pk.object_id ``` 此语句将生成所有表的 CREATE TABLE 语句和主键约束。如果还需要生成索引和其他约束的语句，可以参考以下示例： ``` SELECT 'CREATE ' + CASE WHEN is_unique_constraint = 1 THEN 'UNIQUE ' ELSE '' END + 'INDEX ' + ix.name + ' ON ' + schema_name(tab.schema_id) + '.' + tab.name + '(' + STUFF((SELECT ', ' + col.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END FROM sys.index_columns AS ic JOIN sys.columns AS col ON ic.column_id = col.column_id AND ic.object_id = col.object_id WHERE ic.object_id = ix.object_id AND ic.index_id = ix.index_id ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '') + ')' + CASE WHEN ix.create_date <> ix.modify_date THEN '/* Created: ' + CONVERT(NVARCHAR(30),ix.create_date,120) + ', Modified: ' + CONVERT(NVARCHAR(30),ix.modify_date,120) + ' */' ELSE '' END AS create_index_script FROM sys.indexes AS ix JOIN sys.tables AS tab ON ix.object_id = tab.object_id WHERE is_primary_key = 0 AND is_unique_constraint = 0 AND is_disabled = 0 AND ix.type <> 0 ``` 此语句将生成所有非聚集索引的 CREATE INDEX 语句。如果还需要生成其他约束的语句，可以使用类似的方法查询 sys.objects 和其他系统视图。