sql查看数据字典（表结构）SELECT (case when a.colorder=1 then else null end) 表名,a.colorder 字段序号, 字段名,(case when COLUMNPROPERTY( a.id,,'IsIdentity')=1 then '√'else '' end) 标识,(case when (SELECT count(*) FROM sysobjectsWHERE (name in (SELECT name FROM sysindexesWHERE (id = a.id) AND (indid in(SELECT indid FROM sysindexkeysWHERE (id = a.id) AND (colid in(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = )))))))AND (xtype = 'PK'))>0 then '√' else '' end) 主键, 类型,a.length 占⽤字节数,COLUMNPROPERTY(a.id,,'PRECISION') as 长度,isnull(COLUMNPROPERTY(a.id,,'Scale'),0) as ⼩数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]FROM syscolumns aleft join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and <>'dtproperties'left join syscomments e on a.cdefault=e.idleft join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_idleft join sys.extended_properties f on d.id=f.class and f.minor_id=0where is not null--WHERE ='要查询的表' --如果只查询指定表,加上此条件order by a.id,a.colorder 转：/ynbt/archive/2012/07/16/2593389.html查看表⾏数，⼤⼩；查看索引因⼦（扫描密度，平均页密度⽐例较低，需重组），重组索引；select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages)+'kb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,rows,'DBCC showcontig('''+object_name(id)+''')' ,'--DBCC DBREINDEX('''+object_name(id)+''')','select top 10 * from '+object_name(id)from sysindexes where indid<=1 order by rows DESCView Code表添加字段和描述alter table FI_repsource add tt_orderstate VARCHAR(50) null;EXECUTE sp_addextendedproperty N'MS_Description', N'订单号状态', N'user', N'dbo', N'table', N'FI_repsource', N'column', N'tt_orderstate' View Code查看sql执⾏慢的语句--慢的sql语句SELECT(total_elapsed_time / execution_count)/1000 N'平均时间ms',total_elapsed_time/1000 N'总花费时间ms',total_worker_time/1000 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/2) + 1,((CASE statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END- qs.statement_start_offset)/2) + 1) N'执⾏语句',creation_time N'语句编译时间',last_execution_time N'上次执⾏时间'FROMsys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) stWHERESUBSTRING(st.text, (qs.statement_start_offset/2) + 1,((CASE statement_end_offsetWHEN -1 THEN DATALENGTH(st.text)ELSE qs.statement_end_offset END- qs.statement_start_offset)/2) + 1) not like '%fetch%'ORDER BYtotal_elapsed_time / execution_count DESC;View Code批量删除数据（数据太多，指定删除top）declare @i intset @i=0while @i<6500000begindelete tb_name from tb_name aa join (select top 1000 id from tb_name where id<@i order by id asc) bb on aa.id=bb.idset @i=@i+1000print('@i='+convert(varchar,@i));endprint('end---')View Code锁单操作（每次只取⼀条）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}'";View Code批量复制同结构表数据IF OBJECT_ID(N'tempdb..#temp') IS NOT NULLDROP TABLE #tempSELECT*INTO #temp FROM SYSOBJECTS WHERE TYPE ='U'ORDER BY name asc--SELECT * FROM #tempDECLARE@tbName varchar(max)DECLARE@colName varchar(max) --声明@colName变量DECLARE@NcolName varchar(max)DECLARE@sql VARCHAR(MAX)DECLARE@sqlall VARCHAR(MAX)DECLARE@id INTDECLARE@count INTSET@sqlall='';SET@count=0;WHILE EXISTS(SELECT name FROM #temp)BEGINSELECT TOP1@id= id,@tbName=name FROM #temp;SET@colName=''SET@NcolName=''SET@sql=''--判断是否⾃增SELECT@count=count(1) from syscolumns where id=object_id(@tbName) and COLUMNPROPERTY(id,name,'IsIdentity')=1--PRINT(@count)--PRINT(@tbName)SELECT@colName=@colName+COLUMN_NAME+','FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@tbNameSET@NcolName=subString(@colName,1,len(@colName)-1);IF(@count>0) SET@sql+='Set Identity_Insert '+@tbName+' on ';SET@sql+='insert into '+@tbName+'('+@NcolName+' )';SET@sql+='select '+@NcolName+' from [192.168.2.106].[数据库名称].[dbo].'+@tbName+'(NOLOCK) ';IF(@count>0) SET@sql+='Set Identity_Insert '+@tbName+' off ;';PRINT(@sql)--EXEC(@sql); --执⾏复制SET@sqlall+=''+@sql;DELETE FROM #temp WHERE id=@id;END--PRINT( @sqlall)View CodeSqlServer数据库表⽣成C# Model实体类SQL语句等常⽤sqlSELECT (case when a.colorder=1then else null end) 表名,a.colorder 字段序号, 字段名,(case when COLUMNPROPERTY( a.id,,'IsIdentity')=1then'√'else''end) 标识,(case when (SELECT count(*) FROM sysobjectsWHERE (name in (SELECT name FROM sysindexesWHERE (id = a.id) AND (indid in(SELECT indid FROM sysindexkeysWHERE (id = a.id) AND (colid in(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = )))))))AND (xtype ='PK'))>0then'√'else''end) 主键, 类型,a.length 占⽤字节数,COLUMNPROPERTY(a.id,,'PRECISION') as长度,isnull(COLUMNPROPERTY(a.id,,'Scale'),0) as⼩数位数,(case when a.isnullable=1then'√'else''end) 允许空,isnull(e.text,'') 默认值,isnull(g.[value], '') AS[说明]---------------,', { field: '''++''', title: '''+ISNULL( CAST(g.[value]as VARCHAR(max)),'') +''', sort: true }'AS[layui_JS],', { field: '''++''', title: '''+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+''', sort: true '+CASE WHEN ='datetime'THEN', templet: function (d) { return LayuiDateToTime(d.ProductDate, ''yyyy-MM-dd HH:mm:ss'') }'WHEN ='date'THEN', templet: function (d) { return LayuiDateToTime(d.ProductDate, ''yyyy-MM-dd'') }'ELSE''END+'}'AS[layui_JS2],+'=_valveFeeModel.'++','AS[C#实体赋值],CASE WHEN ='datetime'OR ='date'THEN'laydate.render({ elem: ''#'++'_Min'', calendar: true, type: '''++''', trigger: ''click'' });laydate.render({ elem: ''#'++'_Max'', calendar: true, type: '''++''', trigger: ''click'' });laydate.render({ elem: ''#'++''', calendar: true, type: '''++''', trigger: ''click'' });'ELSE''END AS[layuid_JSdatetime],CASE WHEN ='datetime'OR ='date'THEN'<div class="layui-inline"><label class="layui-form-label">'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'</label><div class="layui-input-inline"><input type="text" id="'++'_Min" name="'++'_Min" autocomplete="off" class="layui-input"/></div><div class="layui-form-mid">-</div><div class="layui-input-inline"><input type="text" id="'++'_Max" name="'++'_Max" autocomplete="off" class="layui-input"/></div></div>'ELSE' <div class="layui-inline"><label class="layui-form-label">'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'</label><div class="layui-input-inline"><input type="text" name="'++'" id="'++'" autocomplete="off" class="layui-input" /></div></div>'END AS[HTML搜索框]--,'<div class="layui-form-item">-- <div class="layui-inline">-- <label class="layui-form-label">'+ CASE WHEN g.[value] IS NULL THEN ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'</label>-- <div class="layui-input-inline">-- <input type="text" name="'++'" id="'++'" autocomplete="off" class="layui-input" />-- </div>-- </div>--</div>' AS [HTML搜索框],'<div class="layui-inline"><label class="layui-form-label">'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'</label><div class="layui-input-inline"><select name="'++'" lay-search id="'++'"><option value="">请选择</option><% foreach (var item in _'++'Dic){ %><option value="<%=item.Key %>"><%=item.Value %></option><% } %></select></div></div>'AS[HTML搜索框_下拉],CASE WHEN ='datetime'OR ='date'THEN'if (!dic.ContainsKey("'++'") || !decimal.TryParse(dic["'++'"].paramValue, out amt))return "'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'不能为空";'WHEN ='int'OR ='smallint'OR ='tinyint'THEN'if (!dic.ContainsKey("'++'") || !int.TryParse(dic["'++'"].paramValue, out id))return "'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'不能为空";'WHEN ='bigint'THEN'if (!dic.ContainsKey("'++'") || !long.TryParse(dic["'++'"].paramValue, out longid))return "'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'不能为空";'WHEN ='decimal'THEN'if (!dic.ContainsKey("'++'") || !decimal.TryParse(dic["'++'"].paramValue, out decimalid))return "'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'不能为空";'ELSE'if (!dic.ContainsKey("'++'"))return "'+CASE WHEN g.[value]IS NULL THEN ELSE ISNULL( CAST( g.[value]AS VARCHAR(max)),'')END+'不能为空";'END AS[C#检查]FROM syscolumns aleft join systypes b on a.xtype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U'and <>'dtproperties'left join syscomments e on a.cdefault=e.idleft join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_idleft join sys.extended_properties f on d.id=f.class and f.minor_id=0where is not null--and in('Doc_VaseAudit','ValveFeeDetail','ValveFee','LogTrade','CompBalance','ValveFeeConfig') --如果只查询指定表,加上此条件--order by a.id,a.colorderORDER BY a.id,a.colorder-----------------------------------------------------------------------------------------------\\\--SELECT (case when a.colorder=1 then else null end) 表名,--a.colorder 字段序号, 字段名,--(case when COLUMNPROPERTY( a.id,,'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 = )))))))--AND (xtype = 'PK'))>0 then '√' else '' end) 主键, 类型,a.length 占⽤字节数,--COLUMNPROPERTY(a.id,,'PRECISION') as 长度,--isnull(COLUMNPROPERTY(a.id,,'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 <>'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 is not null----and in('','Doc_VaseAudit','') --如果只查询指定表,加上此条件----order by a.id,a.colorder--ORDER BY a.id,a.colorder----------------------------------------------------------------------添加备注--alter table t_system_rebatesetting add currency CHAR(3) null;--EXECUTE sp_addextendedproperty N'MS_Description', N'结算币种(如：CNY(默认),USD,HKD 等', N'user', N'dbo', N'table', N't_system_rebatesetting', N'column', N'currency'--alter table t_system_multirebatesetting add currency CHAR(3) null;--EXECUTE sp_addextendedproperty N'MS_Description', N'结算币种(如：CNY(默认),USD,HKD 等', N'user', N'dbo', N'table', N't_system_multirebatesetting', N'column', N'currency'--------更新备注----EXECUTE sp_updateextendedproperty N'MS_Description', N'操作类型(1:充值 2:订单⽀付 3:信⽤额度配置 4:销帐 5:订单拒单,6:订单取消 7:提现 8:订单退票)', N'user', N'dbo', N'table', N't_log_trade', N'column', N'tradetype'----EXECUTE sp_updateextendedproperty N'MS_Description', N'操作类型(1:充值 2:订单⽀付 3:信⽤额度配置 4:销帐 5:订单拒单,6:订单取消 7:提现 8:订单退票)', N'user', N'dbo', N'table', N't_log_optpricetrade', N'column', N'opttype'------C# 赋值---------------------------SELECT 'model.'+name+'=item.'+name +';'+CHAR(39)+CHAR(34)+CHAR(10)--FROM syscolumns--WHERE id in( SELECT id--FROM sysobjects--WHERE (name = 't_order'))--FOR XML PATH('')---------------查看表 --------------select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages)+'kb' used,--8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,rows--,'DBCC showcontig('''+object_name(id)+''')' ,'--DBCC DBREINDEX('''+object_name(id)+''')'--from sysindexes where indid<=1 order by rows desc---删除某列----------------------------ALTER TABLE 表名 DROP COLUMN 新列名----PowerDesigner导出SQL时如何添加注释---------------------------------https:///article/47a29f24652e44c0142399c3.html------------第⼀步，按“Database”>>“Edit Current DBMS..”--第⼆步，按“Script”>>“Object”>>“Column”>>“ColumnComment”打开，然后将value中的信息改成--EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'%COMMENT%' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'[%QUALIFIER%]%TABLE%', @level2type=N'COLUMN',@ --，最后点击“确定”，--表说明同理：--comment on table [%QUALIFIER%]%TABLE% is [%COMMENT%?--%.q:COMMENT%:null] "Table"=>"TableComment"改为：--EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'%COMMENT%' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'[%QUALIFIER%]%TABLE%', @level2type=null,@level2na ----PowerDesigner导出C#实体类（带备注）---------https:///shuai7boy/p/10219963.html------------.if (%isGenerated%) and (%isValidAttribute%)--/// \<summary\>--/// [%Name%\n]\--/// \</summary\>--[%oidDocTag%\n]\--[%customAttributes%\n]\-- .if (%Multiple% == false) and (%isIndexer% == false)--[%visibility% ][%flags% ]%dataType%--.convert_name(%fieldCode%,,"_",FirstUpperChar)-- { get; set; } [ = %InitialValue%;]-- .else--[%visibility% ][%flags% ]%dataType%[%arraySize%]--.convert_name(%fieldCode%,,"_",FirstUpperChar)-- { get; set; } [ = %InitialValue%;]-- .endif--.endif-----------------------------------------------------------------------------------------------------1、sqlserver⽤语句给表注释--EXECUTE sp_addextendedproperty N'MS_Description', N'表注释', N'user', N'dbo', N'table', N'表名', NULL, NULL--2、sqlserver⽤语句给表的“字段”注释--EXECUTE sp_addextendedproperty N'MS_Description', N'字段注释', N'user', N'dbo', N'table', N'表名', N'column', N'字段名'--3、查看sqlserver注释--SELECT AS table_name, AS column_name,--C.value AS column_description--FROM sys.tables A--INNER JOIN sys.columns B ON B.object_id = A.object_id--LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id--WHERE = '表名'----------SqlServer数据库表⽣成C# Model实体类SQL语句--------------- 使⽤临时表（遍历表名称，进⾏⽣成model-- 创建临时表IF OBJECT_ID('tempdb.dbo.#tempTable','U') IS NOT NULLDROP TABLE dbo.#tempTable;GO--SELECT * FROM sys.tables ---遍历表名称SELECT*INTO dbo.#tempTableFROM sys.tables WHERE1=1--AND name IN('') --指定表名称--SELECT * FROM dbo.#tempTable;-- 声明变量declare@TableName sysname ;declare@Result varchar(max);SET@Result='';--循环WHILE EXISTS(SELECT name FROM dbo.#tempTable)BEGIN-- 也可以使⽤top 1SELECT TOP1@TableName= name FROM dbo.#tempTable;--逻辑处理beginSET@Result+='/// <summary>/// '+@TableName+'/// </summary>public class '+@TableName+'{'select@Result=@Result+'/// <summary>/// '+CONVERT(NVARCHAR(500), ISNULL(ColName, '⽆')) +'/// </summary>public '+ ColumnType + NullableSign +''+ ColumnName +' { get; set; }'from(SELECTreplace(, '', '_') ColumnName,column_id ColumnId,prop.value ColName,case when'bigint'then'long'when'binary'then'byte[]'when'bit'then'bool'when'char'then'string'when'date'then'DateTime'when'datetime'then'DateTime'when'datetime2'then'DateTime'when'datetimeoffset'then'DateTimeOffset'when'decimal'then'decimal'when'float'then'float'when'image'then'byte[]'when'int'then'int'when'money'then'decimal'when'nchar'then'char'when'ntext'then'string'when'numeric'then'decimal'when'nvarchar'then'string'when'real'then'double'when'smalldatetime'then'DateTime'when'smallint'then'short'when'smallmoney'then'decimal'when'text'then'string'when'time'then'TimeSpan'when'timestamp'then'DateTime'when'tinyint'then'byte'when'uniqueidentifier'then'Guid'when'varbinary'then'byte[]'when'varchar'then'string'else'UNKNOWN_'+ end ColumnType,casewhen col.is_nullable =1and in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then'?'else''end NullableSignfrom sys.columns coljoin sys.types typ oncol.system_type_id = typ.system_type_id AND er_type_id = er_type_idLEFT JOIN sys.extended_properties prop ON col.object_id= prop.major_id AND col.column_id = prop.minor_idwhere object_id=object_id(@TableName)) t--order by ColumnIdset@Result=@Result+'}'--逻辑处理endDELETE FROM dbo.#tempTable WHERE name=@TableName; --删除临时表中已处理数据ENDSELECT@Resultprint@ResultView CodeC#将集合若⼲组（分页）///<summary>///将集合若⼲组///</summary>///<param name="source">数据集</param>///<param name="pageSize">每⼀组⼤⼩</param>private List<List<T>> SpliteSourceBySize<T>(List<T> source, int pageSize) where T : class, new() {// 计算组数List<List<T>> pages = new List<List<T>>();//页码 0也就是第⼀条int pageNum = 0;while (pageNum * pageSize < source.Count){//分页var query = source.Skip(pageNum * pageSize).Take(pageSize).ToList();pages.Add(query);pageNum++;}return pages;}View Code。
