sqlserver 常用查询

1. 查看表的主键

EXEC sp_pkeys @table_name='表名'

2. 获取一个表的列

--2005 以上版本

select distinct(sc.colorder), sc.name, sc.xusertype, sc.length, 
  sc.isnullable, sc.xprec, sc.xscale, sc.xtype, 
  COLLATIONPROPERTY(sc.collation, 'CodePage') as 'CodePage', 
  pc.leaf_offset, pc.leaf_null_bit, pc.leaf_bit_position,
  dc.definition as 'default', dc.name as 'constraint name', 
  ic.is_identity, ic.seed_value, ic.increment_value 
from syscolumns sc 
left join sys.system_internals_partitions p 
	 on sc.id = p.object_id 
	 and p.index_id in (0, 1)   
left join sys.system_internals_partition_columns pc 
	 on p.partition_id = pc.partition_id 
	 and sc.colorder = pc.partition_column_id 
	 and pc.is_dropped = 0 
left join sys.default_constraints dc 
  on sc.cdefault = dc.object_id 
left join sys.identity_columns ic 
  on sc.id = ic.object_id 
  and sc.colorder = ic.column_id 
where 
	 sc.id = object_id('dbo.表名')  	   
order by colorder asc   

--2000版本
select c.colid, c.name, c.xusertype, c.length, c.isnullable, c.xprec, c.xscale, c.xtype, 
  COLLATIONPROPERTY(collation, 'CodePage') as 'CodePage', 
  xoffset as leaf_offset, c.colorder as leaf_null_bit, bitpos as leaf_bit_position, 
  cm.text as 'default', o.name, autoval, c.iscomputed, cm2.text as c_def 
from syscolumns c 
left join syscomments cm 
  on cm.id = c.cdefault 
left join sysobjects o 
  on c.cdefault = o.id 
left join syscomments cm2 
  on c.id = cm2.id 
  and c.colid = cm2.number 
where c.id = object_id('dbo.表名')  

3. 获取所有表的列信息

select distinct(sc.colorder), sc.id as objd, sc.name, sc.xusertype, sc.length, 
  sc.isnullable, sc.xprec, sc.xscale, sc.xtype, 
  COLLATIONPROPERTY(sc.collation, 'CodePage') as 'CodePage', 
  pc.leaf_offset, pc.leaf_null_bit, pc.leaf_bit_position,
  dc.definition as 'default', dc.name as 'constraint name', 
  ic.is_identity, ic.seed_value, ic.increment_value 
from syscolumns sc
left join sys.system_internals_partitions p 
	 on sc.id = p.object_id 
	 and p.index_id in (0, 1)   
left join sys.system_internals_partition_columns pc 
	 on p.partition_id = pc.partition_id 
	 and sc.colorder = pc.partition_column_id 
	 and pc.is_dropped = 0 
left join sys.default_constraints dc 
  on sc.cdefault = dc.object_id 
left join sys.identity_columns ic 
  on sc.id = ic.object_id 
  and sc.colorder = ic.column_id 
order by objd, colorder asc 

4. 查看SQL日志的文件大小

dbcc sqlperf(logspace)

5. 前1000个在 startLSN-endLSN 的事务

select top 1000 * from fn_dblog (@startLSN,@endLSN)

• @startLSN：事务开始的LSN
• @endLSN：交易结束的LSN

6. 查询是否有阻塞会话

--通过会话session_id查询导致该会话被阻塞的进程id(blocking_session_id),然后通过blocking_session_id值查询会话登录用户。

select session_id,status,command,blocking_session_id from sys.dm_exec_requests where blocking_session_id > 0

7. 查询阻塞SQL语句

--根据session_id查询会话SQL语句

select er.session_id, CAST(csql.text AS varchar(255)) AS CallingSQL from master.sys.dm_exec_requests er WITH (NOLOCK) CROSS APPLY fn_get_sql(er.sql_handle) csql where er.session_id in (xxx,xxx)

8. 查询执行阻塞语句的用户

--根据session_id来查询会话登录用户

select * from sys.dm_exec_sessions where session_id in (xxx,xxx);

9.查看sqlserver字符集和字符集编号

SELECT SERVERPROPERTY('Collation') SELECT COLLATIONPROPERTY('Chinese_PRC_CI_AS', 'CodePage');

后续待更