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:被扩展的存储过程