在SqlServer中如何查看数据库的对象,比如我想查看数据中有哪些表,视图,函数,过程。
(通过sql语句来查,而不是通过企业管理器看)ps:主要是因为我有两个数据库,库结构很相似,我想对比一下,找出对象的差异。但是对象上千个,挨个找就费了劲了!
(通过sql语句来查,而不是通过企业管理器看)ps:主要是因为我有两个数据库,库结构很相似,我想对比一下,找出对象的差异。但是对象上千个,挨个找就费了劲了!
调试欢乐多
SELECT
C.column_id
,C.name
,type = T.name + CASE T.user_type_id WHEN 41 THEN '('+CAST(C.scale AS VARCHAR) +')' -- time
WHEN 42 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetime2
WHEN 43 THEN '('+CAST(C.scale AS VARCHAR) +')' -- datetimeoffset
WHEN 106 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- decimal
WHEN 108 THEN '('+CAST(C.precision AS VARCHAR)+','+ CAST(C.scale AS VARCHAR) +')' -- numeric
WHEN 165 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varbinary
WHEN 167 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- varchar
WHEN 173 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- binary
WHEN 175 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- char
WHEN 231 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- nvarchar
WHEN 239 THEN '('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')' -- nchar
ELSE ''
END
,C.is_identity
,C.is_nullable
,DefaultText = ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'')
FROM sys.columns C
INNER JOIN sys.types T ON C.user_type_id = T.user_type_id
LEFT JOIN sys.default_constraints D ON D.[object_id] =C.default_object_id
AND D.parent_object_id = C.[object_id] AND D.parent_column_id = C.column_id
WHERE C.[object_id] = OBJECT_ID('你的表名');
select * from sysobjects
where xtype='u'
字段
select * from syscolumns
where id=object_id('表名')
视图
select * from sysobjects
where xtype='v'
过程
select * from sysobjects
where xtype='p'
表:SELECT * FROM SYS.TABLES
视图:SELECT * FROM SYS.VIEWS
其它以此类推
--比如查看有哪些用户表,name为表名
select name from sysobjects where type='u'name
--------------------------------------------------------------------------------------------------------------------------------
tb
ta
tt
T_Area
student
class
room
...
...
select
[表名]=c.Name,
[表说明]=isnull(f.[value],''),
[列名]=a.Name,
[列序号]=a.Column_id,
[标识]=case when is_identity=1 then '√' else '' end,
[主键]=case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name
join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)
then '√' else '' end,
[类型]=b.Name,
[字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'
when b.Name='xml' then '2^31-1字节/2G'
else rtrim(a.[max_length]) end,
[长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1'
else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,
[小数]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),
[是否为空]=case when a.is_nullable=1 then '√' else '' end,
[列说明]=isnull(e.[value],''),
[默认值]=isnull(d.text,'')
from
sys.columns a
left join
sys.types b on a.user_type_id=b.user_type_id
inner join
sys.objects c on a.object_id=c.object_id and c.Type='U'
left join
syscomments d on a.default_object_id=d.ID
left join
sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
left join
sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1
order by 1
/*
表名 表说明 列名 列序号 标识 主键 类型 字节数 长度 小数 是否为空 列说明 默认值
A stationid 1 varchar 5 5 0 √
A date 2 datetime 8 23 3 √
A rain60min 3 int 4 10 0 √
ag_job job_id 1 char 11 11 0
ag_job job_name 2 varchar 128 128 0 √
ag_job beactive 3 char 2 2 0
ag_job description 4 varchar 255 255 0 √
*/是这样的结果吗?
库1..sysobjects A
full join
库2..sysobjects B
ON A.name=B.name and A.xtype=B.xtype
WHERE A.xtype<>'S' And B.xtype<>'S'
ORDER BY A.Name