sql server 使用sql脚本查询数据库表,数据,结构,约束

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]

 4.查询当前数据库的存储过程名和内容

select o.name,s.text from sysobjects o join syscomments s on o.id=s.id where o.type='p'

 

 

 

可以查询表名,视图名,存储过程名等
select * from sysobjects 
--where xtype='U' --表名
--where xtype='V' --视图名
--where xtype='PK' --主键
--where xtype='P' --存储过程
--where xtype='TR' --触发器

 

    • C检查约束。
    • D默认的约束
    • F外键约束
    • L日志
    • P存储过程
    • PK主键约束
    • RF复制过滤存储过程
    • S系统表格
    • TR触发器
    • U用于表格。
    • UQ独特的约束。
    • V视图
    • X被扩展的存储过程

 

posted @ 2013-03-11 14:32    阅读(301)  评论(0编辑  收藏  举报