sqlserver小技巧

1取得異常訊息

select description from master..sysmessages where error = @@error 

2根據主鍵查詢表的列名

select name from syscolumns where id=object_id('表名') and colid in(
   select colid from sysindexkeys where object_id('表名')=id and indid in(
    select indid from sysindexes where object_id('表名')=id and name in(
     select name from sysobjects where xtype='PK' and parent_obj=object_id('表名')
   )))

3

exec sp_MSForEachTable
@precommand=N'
create table ##(
id int identity,
表名 sysname,
欄位數 int,
記錄數 int,
保留空間 Nvarchar(10),
使用空間 varchar(10),
索引使用空間 varchar(10),
未用空間 varchar(10))',
@command1=N'insert ##(表名,記錄數,保留空間,使用空間,索引使用空間,未用空間) exec sp_spaceused ''?''
update ## set 欄位數=(select count(*) from syscolumns where id=object_id(''?'')) where id=scope_identity()',
@postcommand=N'select * from ## where 表名=''[table]'' order by id drop table ##'
4

select A.XX,A.XX from A where not exists (select 1 from B where A.id=B.id)
與select A.XX,A.XX from A where not exists (select * from B where A.id=B.id)

5查看每表記錄數

exec sp_msforeachtable 'select ''?'' as ''表名'',(select count(1) from ?) as ''行數'''

select DISTINCT o.name,i.rows from sysobjects o
inner join sysindexes i
on o.id=i.id
where xtype='u' and OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and i.rows>0

select DISTINCT a.name,c.rows
from  sysobjects  a , syscolumns b , sysindexes c
where  a.id=b.id and a.id=c.id
and  a.type='u'  and c.rows<>0

CREATE TABLE dbo.#(tab_name sysname)

exec sp_msforeachtable 'IF (SELECT COUNT(*) FROM (SELECT TOP 1 * FROM ?) a)>0 INSERT INTO # VALUES(''?'')'

SELECT COUNT(*) FROM #