SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM syscolumns a
left join systypes b on a.xtype=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
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder
转：http://www.cnblogs.com/ynbt/archive/2012/07/16/2593389.html
查看表行数，大小； 查看索引因子（扫描密度，平均页密度比例较低，需重组），重组索引；
select object_name(id) tablename,*reserved/ reserved,rtrim(*dpages)+'kb' used,
*(reserved-dpages)/ unused,*dpages/-rows/*minlen/ free,rows ,'DBCC showcontig('''+object_name(id)+''')' ,'--DBCC DBREINDEX('''+object_name(id)+''')'
,'select top 10 * from '+object_name(id)
from sysindexes where indid<= order by rows DESC
表添加字段和描述
alter table FI_repsource add tt_orderstate VARCHAR() null;
EXECUTE sp_addextendedproperty N'MS_Description', N'订单号状态', N'user', N'dbo', N'table', N'FI_repsource', N'column', N'tt_orderstate'
查看sql执行慢的语句
--慢的sql语句
SELECT (total_elapsed_time / execution_count)/ N'平均时间ms' ,total_elapsed_time/ N'总花费时间ms' ,total_worker_time/ N'所用的CPU总时间ms' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' ,execution_count N'执行次数' ,SUBSTRING(st.text, (qs.statement_start_offset/) + , ((CASE statement_end_offset WHEN - THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/) + ) N'执行语句' ,creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st WHERE SUBSTRING(st.text, (qs.statement_start_offset/) + , ((CASE statement_end_offset WHEN - THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/) + ) not like '%fetch%' ORDER BY total_elapsed_time / execution_count DESC;
批量删除数据（数据太多，指定删除top）
declare @i int
set @i=
while @i<
begin
delete tb_name from tb_name aa join (select top id from tb_name where id<@i order by id asc) bb on aa.id=bb.id
set @i=@i+
print('@i='+convert(varchar,@i));
end
print('end---')
锁单操作（每次只取一条）
string guid = Guid.NewGuid().ToString();
string sqldata = @"update t_order_deposit set [lockname]='{0}',locktime='{1}' from t_order_deposit aa join (select top 1 id from t_order_deposit WHERE state='N' and lockname is null order by locktime asc) bb on aa.id=bb.id;
SELECT id,orderid,orderno,orderamount,pcc,pnr from t_order_deposit where lockname='{0}'";