2023年7月13日发(作者：)

sql脚本查询数据库表,数据,结构,约束等操作的⽅法1.查询当前数据库所有表代码如下:复制代码SELECT

_id AS TableId,

TableName= ,

TableDesc=

FROM s C

INNER JOIN s O

ON C.[object_id]=O.[object_id]

AND ='U'

AND _ms_shipped=0

INNER JOIN T

ON _type_id=_type_id

LEFT JOIN ed_properties PTB

ON =1

AND _id=0

AND C.[object_id]=_id

WHERE _id=1

ORDER BY TableName

2.查询当前表所有字段，数据，约束代码如下:复制代码select

tabName=,

columnLine=_id,

columnName=,

typeNum=,

typeLength=_length,

fState=ISNULL(,N''),

isAbleNull=CASE WHEN _nullable=1 THEN N'√'ELSE N'' END,

defaultData=ISNULL(tion,N''),

isIdentity=CASE WHEN _identity=1 THEN N'√'ELSE N'' END,

isPrimary=case when exists(SELECT 1 FROM sysobjects where xtype='PK' andparent_obj=c.[object_id] and name in (

SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeysWHERE id = c.[object_id] AND colid=_id))) then '√' else '' end,

isForeign=case when exists(select * from sysforeignkeys fk where C.[object_id]=AND _id=)then '√' else '' end,

TabForeignName=ISNULL(,N''),

OutNameCol=ISNULL(,N'')

FROM s C

INNER JOIN s O

ON C.[object_id]=O.[object_id]

AND ='U'

AND _ms_shipped=0

INNER JOIN T

ON _type_id=_type_id

left JOIN ed_properties G

ON C.[object_id]=_id and _id=_id

LEFT JOIN t_constraints D ON C.[object_id]=_object_id

AND _id=_column_id

AND t_object_id=D.[object_id]

left join sysforeignkeys fk

on C.[object_id]=

and _id=

LEFT JOIN -- 索引及主键信息

(

SELECT

,

,

FKName=,

ns=

FROM sysforeignkeys IDX

INNER JOIN s O

ON =O.[object_id]

AND ='U'

AND _ms_shipped=0

left join syscolumns ss

on =

and =

)IDX

ON C.[object_id]=

AND _id=ERE =N'{0}' ------要查询的表名ORDER BY ,_id3.字段要加单引号varchar,char,nvarchar,nchar,text,ntext,datetime不需要加int,numeric,bit 不需要加带长度：[binary],[char],[decimal],[nchar],[numeric],[nvarchar],[varbinary][varchar]不⽤带：[bigint],[bit],[datetime],[float],[image],[int],[xml],[timestamp],[tinyint],

[uniqueidentifier],[money],[ntext],[real],[smalldatetime],[smallint],[smallmoney],

[sql_variant],[text]