sql脚本查询数据库表,数据,结构,约束等操作的方法

2022-05-24 0 1,056

1.查询当前数据库所有表

复制代码 代码如下:

SELECT

    O.object_id AS TableId,

    TableName=O.name  ,

    TableDesc= O.type

FROM sys.columns C

    INNER JOIN sys.objects O

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

            AND O.type=’U’

            AND O.is_ms_shipped=0

    INNER JOIN sys.types T

        ON C.user_type_id=T.user_type_id

    LEFT JOIN sys.extended_properties PTB

        ON PTB.class=1

            AND PTB.minor_id=0

            AND C.[object_id]=PTB.major_id

WHERE C.column_id=1

ORDER BY TableName

2.查询当前表所有字段,数据,约束

复制代码 代码如下:

select   

tabName=O.NAME,

columnLine=C.column_id,

columnName=C.name,

typeNum=T.name,

typeLength=C.max_length,

fState=ISNULL(G.value,N”),

isAbleNull=CASE WHEN C.is_nullable=1 THEN N’√’ELSE N” END,

defaultData=ISNULL(D.definition,N”),

isIdentity=CASE WHEN C.is_identity=1 THEN N’√’ELSE N” END,

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

             SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = c.[object_id] AND colid=c.column_id))) then ‘√’ else ” end,

isForeign=case when exists(select * from sysforeignkeys fk where C.[object_id]=FK.fkeyid AND C.column_id=FK.fkey)then ‘√’ else ” end,

TabForeignName=ISNULL(IDX.FKName,N”),

OutNameCol=ISNULL(IDX.ns,N”)

FROM sys.columns C

INNER JOIN sys.objects O

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

            AND O.type=’U’

            AND O.is_ms_shipped=0

INNER JOIN sys.types T

        ON C.user_type_id=T.user_type_id

left JOIN sys.extended_properties G

        ON C.[object_id]=G.major_id and c.column_id=g.minor_id

LEFT JOIN sys.default_constraints D  

        ON C.[object_id]=D.parent_object_id

            AND C.column_id=D.parent_column_id

            AND C.default_object_id=D.[object_id]

left join sysforeignkeys fk

        on C.[object_id]=FK.fkeyid

            and C.column_id=FK.fkey

 LEFT JOIN                       — 索引及主键信息

    (

        SELECT

            IDX.fkeyid,

            IDX.fkey,

            FKName=o.name,

            ns=ss.name

        FROM sysforeignkeys IDX

        INNER JOIN sys.objects O

        ON IDX.rkeyid=O.[object_id]

            AND O.type=’U’

            AND O.is_ms_shipped=0

        left join syscolumns ss

        on IDX.rkeyid=ss.id

            and IDX.RKEY=SS.COLID

    )IDX

        ON C.[object_id]=IDX.fkeyid

            AND C.column_id=IDX.fkey

WHERE O.name=N'{0}’        ——要查询的表名
ORDER BY O.name,C.column_id

3.字段

要加单引号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]

免责声明:
1、本网站所有发布的源码、软件和资料均为收集各大资源网站整理而来;仅限用于学习和研究目的,您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 不得使用于非法商业用途,不得违反国家法律。否则后果自负!

2、本站信息来自网络,版权争议与本站无关。一切关于该资源商业行为与www.niceym.com无关。
如果您喜欢该程序,请支持正版源码、软件,购买注册,得到更好的正版服务。
如有侵犯你版权的,请邮件与我们联系处理(邮箱:skknet@qq.com),本站将立即改正。

NICE源码网 MsSql sql脚本查询数据库表,数据,结构,约束等操作的方法 https://www.niceym.com/61232.html