如何使用SQL语句导出表结构脚本？
如何使用SQL语句导出表结构脚本？
强调：必须使用SQL脚本，我想把这SQL语句写入我的程序中。
谢谢
[解决办法]
--老大写的SQL
SELECT TableName=o.name,OWNER=USER_NAME(o.uid),TableDescription=ISNULL(ptb.value,N ' '),
FieldId=c.colid,FieldName=c.name,
FieldType=QUOTENAME(t.name)
+CASE
WHEN t.name IN (N 'decimal ',N 'numeric ')
THEN N '( '+CAST(c.prec as varchar)+N ', '+CAST(c.scale as varchar)+N ') '
WHEN t.name=N 'float '
OR t.name like N '%char '
OR t.name like N '%binary '
THEN N '( '+CAST(c.prec as varchar)+N ') '
ELSE N ' ' END
+CASE WHEN c.isnullable=1 THEN N ' ' ELSE N ' NOT ' END+N ' NULL ',
FieldDescription=ISNULL(pfd.value, ' '),
DefileLength=c.length,
FieldDefault=ISNULL(df.text,N ' '),
IsIDENTITY=COLUMNPROPERTY(o.id,c.name,N 'IsIdentity '),
IsComputed=COLUMNPROPERTY(o.id,c.name,N 'IsComputed '),
IsROWGUID=COLUMNPROPERTY(o.id,c.name,N 'IsRowGuidCol '),
IsPrimaryKey=CASE WHEN opk.xtype IS NULL THEN 0 ELSE 1 END
FROM sysobjects o
JOIN syscolumns c
ON c.id=o.id
AND OBJECTPROPERTY(o.id,N 'IsUserTable ')=1
JOIN systypes t
ON t.xusertype=c.xusertype
LEFT JOIN syscomments df
ON df.id=c.cdefault
LEFT JOIN sysproperties ptb
ON ptb.id=o.id and ptb.smallid=0
LEFT JOIN sysproperties pfd
ON pfd.id=o.id and pfd.smallid=c.colid
LEFT JOIN sysindexkeys idxk
ON idxk.id=o.id
AND idxk.colid=c.colid
LEFT JOIN sysindexes idx
ON idx.indid=idxk.indid
AND idx.id=idxk.id
AND idx.indid NOT IN(0,255)
LEFT JOIN sysobjects opk
ON opk.parent_obj=o.id
AND opk.name=idx.name
AND OBJECTPROPERTY(opk.id,N 'IsPrimaryKey ')=1
ORDER BY o.name,c.colid
[解决办法]
SELECT
TableName=o.name,
--OWNER=USER_NAME(o.uid),
TableDescription=ISNULL(ptb.value,N ' '),
FieldId=c.column_id,FieldName=c.name,
FieldType=QUOTENAME(t.name)
+CASE
WHEN t.name IN (N 'decimal ',N 'numeric ')
THEN N '( '+CAST(c.precision as varchar)+N ', '+CAST(c.scale as varchar)+N ') '
WHEN t.name=N 'float '
OR t.name like N '%char '
OR t.name like N '%binary '
THEN N '( '+CAST(c.precision as varchar)+N ') '
ELSE N ' ' END
+CASE WHEN c.is_nullable=1 THEN N ' ' ELSE N ' NOT ' END+N ' NULL ',
FieldDescription=ISNULL(pfd.value, ' '),
DefileLength=c.max_length,
FieldDefault=ISNULL(df.text,N ' '),
IsIDENTITY=COLUMNPROPERTY(o.object_id,c.name,N 'IsIdentity '),
IsComputed=COLUMNPROPERTY(o.object_id,c.name,N 'IsComputed '),
IsROWGUID=COLUMNPROPERTY(o.object_id,c.name,N 'IsRowGuidCol '),
IsPrimaryKey=CASE WHEN opk.type = 'pk ' THEN 0 ELSE 1 END
FROM sys.objects o
JOIN sys.columns c
ON c.object_id=o.object_id
AND OBJECTPROPERTY(o.object_id,N 'IsUserTable ')=1
JOIN sys.types t
ON t.user_type_id=c.user_type_id
LEFT JOIN sys.syscomments df
ON df.id=c.default_object_id
LEFT JOIN sys.extended_properties ptb
ON ptb.major_id = o.object_id
and ptb.minor_id=0
LEFT JOIN sys.extended_properties pfd
ON pfd.major_id=o.object_id and pfd.minor_id=c.column_id
LEFT JOIN sys.sysindexkeys idxk
ON idxk.id=o.object_id
AND idxk.colid=c.column_id
LEFT JOIN sys.sysindexes idx
ON idx.indid=idxk.indid
AND idx.id=idxk.id
AND idx.indid NOT IN(0,255)
LEFT JOIN sys.objects opk
ON opk.parent_object_id =o.object_id
AND opk.name=idx.name
AND opk.type = 'PK '
ORDER BY o.name,c.column_id
改了一下,不知道对不对,自己核对一下
[解决办法]
SELECT 表名=case when a.colorder=1 then d.name else ' ' end,
表说明=case when a.colorder=1 then isnull(f.value, ' ') else ' ' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name, 'IsIdentity ')=1 then '√ 'else ' ' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype= 'PK ' and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√ ' else ' ' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name, 'PRECISION '),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale '),0),
允许空=case when a.isnullable=1 then '√ 'else ' ' end,
默认值=isnull(e.text, ' '),
字段说明=isnull(g.[value], ' '),
创建时间=d.crdate
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype= 'U ' and d.name <> 'dtproperties '
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
--where d.name= '此处可添加要查询的表名 '
Order by a.id,a.colorder
[解决办法]
--参考.
http://blog.csdn.net/zlp321002/archive/2006/12/28/1465180.aspx
