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');
后续待更
哪有什么胜利可言,坚持意味着一切。如想使用请备注转载链接~