数据库中常用查询

1.数据库基本情况

select name from sys.objects where type='U' --表
select name from sys.objects where type='V' --视图
select name from sys.objects where type='P' --存储过程
select name from sys.objects where type='TR'--触发器

--数据库中表名，表数量，表大小
SELECT
TableName = obj.name,
TotalRows = prt.rows,
[SpaceUsed(KB)] = SUM(alloc.used_pages)*8
FROM sys.objects obj
JOIN sys.indexes idx on obj.object_id = idx.object_id
JOIN sys.partitions prt on obj.object_id = prt.object_id
JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
WHERE
obj.type = 'U' AND idx.index_id IN (0, 1)
GROUP BY obj.name, prt.rows
ORDER BY TotalRows DESC

2.查询表结构

SELECT 
    表名       = d.name,
    字段序号   = a.colorder,
    字段名     = a.name,
    字段说明   = isnull(g.[value],''),
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,'')   
FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d 
on  a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id left join sys.extended_properties   g 
on a.id=G.major_id and a.colid=g.minor_id  
where d.name='A'    --表名称
order by  a.id,a.colorder 

3.查询与表相关的存储过程，视图

select distinct name,o.xtype from sysobjects o, syscomments s where o.id = s.id and text like '%A%'

4.查询与字段相关的表

SELECT b.name as TableName,a.name as columnname
From syscolumns a
INNER JOIN sysobjects b ON a.id=b.id
where b.type='U'
AND a.name='A'

5.表字段相同时更新

select 'a.'+name+'=b.'+name+',' from sys.all_columns where object_id=object_id('A') and is_identity=0 for XML path('')