使用MSSQL查询建表语句的正确方法
1. 简介
在使用MSSQL Server时，我们有时需要查询已有表的建表语句。这样可以方便我们在另一个数据库或服务器中创建相同的表结构，免去重复建表的麻烦。如果我们手动编写建表语句，不仅容易出错，还浪费了我们大量的时间。因此，查询已有表的建表语句就显得尤为重要。
2. 使用系统存储过程
MSSQL Server提供了一些系统存储过程，可以用于查询表的信息。其中，我们可以使用sp_help命令来查询表的建表语句。下面是使用sp_help命令查询表[dbo].[users]的建表语句的示例：
sp_help '[dbo].[users]'这样，我们就可以得到表[dbo].[users]的建表语句，如下：
CREATE TABLE [dbo].[users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Gender] [nvarchar](10) NULL,
[Email] [nvarchar](100) NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
可以看到，建表语句中包含了表的字段、类型、约束等信息。我们可以将其复制到另一个MSSQL Server中执行，就可以在新的服务器中创建和原来一样的表结构。
3. 使用查询语句生成建表语句
如果我们不想使用系统存储过程，也可以使用查询语句生成建表语句。下面是一个查询语句的示例，可以查询表[dbo].[users]的建表语句：
SELECT
'CREATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' (' +
STUFF
(
(
SELECT
', ' + QUOTENAME(c.name) + ' ' +
CASE
WHEN c.is_computed = 1 THEN 'AS ' + OBJECT_DEFINITION(c.column_id)
ELSE UPPER(tp.name) +
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', 'numeric', 'decimal') THEN '(' + CASE WHEN c.precision = 0 THEN '18' ELSE CAST(c.precision AS VARCHAR(5)) END + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
ELSE ''
END +
CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +
CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +
CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.definition ELSE '' END +
CASE WHEN ic.index_column_id IS NOT NULL THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(ic.[object_id], c.name, 'SeedValue') AS VARCHAR(5)) + ',' + CAST(IDENTITYPROPERTY(ic.[object_id], c.name, 'IncrementValue') AS VARCHAR(5)) + ')' ELSE '' END
END + CHAR(13) AS [text()]
FROM sys.columns c
JOIN sys.types tp ON c.user_type_id = tp.user_type_id
LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
LEFT JOIN sys.identity_columns ic ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
WHERE c.[object_id] = t.[object_id]
ORDER BY c.column_id
FOR XML PATH('')
), 1, 2, ''
) +
' CONSTRAINT ' + QUOTENAME(f.name) + ' ' + f.[definition] +
')' + CHAR(13) + 'ON ' + QUOTENAME(ds.name) +
CASE
WHEN (t.is_partitioned = 1) THEN
'(' +
SELECT
STUFF
(
(
SELECT
', ' + QUOTENAME(pr.name)
FROM sys.partition_schemes ps
JOIN sys.partition_functions pr ON ps.function_id = pr.[function_id]
WHERE ps.[object_id] = t.[object_id]
ORDER BY ps.[name] DESC
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')
, 1, 2, '') + ')'
ELSE ''
END AS SQL
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
LEFT JOIN sys.filegroups fg ON fg.data_space_id = t.[filestream_data_space_id]
LEFT JOIN sys.partition_schemes ps ON ps.data_space_id = t.[partition_scheme_id]
LEFT JOIN sys.indexes i ON t.[object_id] = i.[object_id] AND i.index_id < 2
LEFT JOIN sys.destination_data_spaces dds ON i.[object_id] = dds.[object_id] AND i.index_id = dds.index_id
LEFT JOIN sys.data_spaces ds ON ds.data_space_id = COALESCE(dds.data_space_id, i.[data_space_id], fg.[data_space_id])
LEFT JOIN sys.foreign_keys f ON f.parent_object_id = t.[object_id]
WHERE t.[name] = 'users' AND s.[name] = 'dbo'
运行以上SQL语句后，会返回表[dbo].[users]的建表语句。
4. 结语
查询已有表的建表语句，可以帮助我们更好地管理数据库和数据表。我们可以使用系统存储过程或者SQL查询语句生成建表语句，非常方便。但需要注意的是，某些复杂的表结构可能无法通过这种方式生成。在这种情况下，我们需要手动编写建表语句或者使用第三方工具。