sql查看数据字典（表结构）

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}'";

巴特西

sql查看数据字典（表结构）

最新文章

热门文章