sqlserver 查看建表语句，生成建表语句 sqlserver show create table
sqlserver 没有类似 mysql的
show create table 语句
项目需求每天备份数据和建表sql，sqlserver虽然自带查看建表语句功能，但是也不能每天上去手动点点点保存啊。
只能拼装出来
直接上sql
select 'create table [' + so.name + '] (' + o.list + ')' + CASE
WHEN tc.Constraint_Name IS NULL THEN ''
ELSE 'ALTER TABLE ' + so.Name +
' ADD CONSTRAINT ' + tc.Constraint_Name +
' PRIMARY KEY ' +
' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from sysobjects so
cross apply
(SELECT
' ['+ column_name +'] ' +
data_type + case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'ntext' then ''
when 'xml' then ''
when 'image' then ''
when 'decimal' then '(' + cast (numeric_precision as varchar) + ', ' + cast (numeric_scale as varchar) + ')'
else coalesce ('('+ case when character_maximum_length = -1 then 'MAX' else cast (character_maximum_length as varchar) end +')', '') end + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name = column_name
and columnproperty(id, name, 'IsIdentity') = 1
) then
'IDENTITY(' +
cast (ident_seed(so.name) as varchar) + ',' +
cast (ident_incr(so.name) as varchar) + ')'
else ''
end + ' ' +
(case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH ('')) o (list)
left join
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH ('')) j (list)
where xtype = 'U'
AND name = 'table_name' -- table_name 换成你要查询的表名
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/94916.html
