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,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 DESC
表添加字段和描述
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'
查看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_offset WHEN -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'上次执行时间' 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/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) not like '%fetch%' ORDER BY total_elapsed_time / execution_count DESC;
批量删除数据（数据太多，指定删除top）
declare @i int set @i=0 while @i<6500000 begin delete 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.id set @i=@i+1000 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}'";
批量复制同结构表数据
IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL DROP TABLE #temp SELECT * INTO #temp FROM SYSOBJECTS WHERE TYPE = 'U' ORDER BY name asc --SELECT * FROM #temp DECLARE @tbName varchar(max) DECLARE @colName varchar(max) --声明@colName变量 DECLARE @NcolName varchar(max) DECLARE @sql VARCHAR(MAX) DECLARE @sqlall VARCHAR(MAX) DECLARE @id INT DECLARE @count INT SET @sqlall=''; SET @count=0; WHILE EXISTS(SELECT name FROM #temp) BEGIN SELECT TOP 1 @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=@tbName SET @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)
SqlServer数据库表生成C# Model实体类SQL语句等常用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 [说明] --------------- ,', { field: '''+a.name+''', title: '''+ ISNULL( CAST(g.[value] as VARCHAR(max)),'') +''', sort: true }' AS [layui_JS] ,', { field: '''+a.name+''', title: '''+ CASE WHEN g.[value] IS NULL THEN a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +''', sort: true '+ CASE WHEN b.name='datetime' THEN ', templet: function (d) { return LayuiDateToTime(d.ProductDate, ''yyyy-MM-dd HH:mm:ss'') }' WHEN b.name='date' THEN ', templet: function (d) { return LayuiDateToTime(d.ProductDate, ''yyyy-MM-dd'') }' ELSE '' END +'}' AS [layui_JS2] ,a.name+'=_valveFeeModel.'+a.name+',' AS [C#实体赋值] ,CASE WHEN b.name='datetime' OR b.name='date' THEN 'laydate.render({ elem: ''#'+a.name+'_Min'', calendar: true, type: '''+b.name+''', trigger: ''click'' }); laydate.render({ elem: ''#'+a.name+'_Max'', calendar: true, type: '''+b.name+''', trigger: ''click'' }); laydate.render({ elem: ''#'+a.name+''', calendar: true, type: '''+b.name+''', trigger: ''click'' });' ELSE '' END AS [layuid_JSdatetime] ,CASE WHEN b.name='datetime' OR b.name='date' THEN '<div class="layui-inline"> <label class="layui-form-label">'+ CASE WHEN g.[value] IS NULL THEN a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'</label> <div class="layui-input-inline"> <input type="text" id="'+a.name+'_Min" name="'+a.name+'_Min" autocomplete="off" class="layui-input"/> </div> <div class="layui-form-mid">-</div> <div class="layui-input-inline"> <input type="text" id="'+a.name+'_Max" name="'+a.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 a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'</label> <div class="layui-input-inline"> <input type="text" name="'+a.name+'" id="'+a.name+'" 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 a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'</label> -- <div class="layui-input-inline"> -- <input type="text" name="'+a.name+'" id="'+a.name+'" 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 a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'</label> <div class="layui-input-inline"> <select name="'+a.name+'" lay-search id="'+a.name+'"> <option value="">请选择</option> <% foreach (var item in _'+a.name+'Dic) { %> <option value="<%=item.Key %>"><%=item.Value %></option> <% } %> </select> </div> </div>' AS [HTML搜索框_下拉] ,CASE WHEN b.name='datetime' OR b.name='date' THEN 'if (!dic.ContainsKey("'+a.name+'") || !decimal.TryParse(dic["'+a.name+'"].paramValue, out amt)) return "'+ CASE WHEN g.[value] IS NULL THEN a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'不能为空";' WHEN b.name='int' OR b.name='smallint' OR b.name='tinyint' THEN 'if (!dic.ContainsKey("'+a.name+'") || !int.TryParse(dic["'+a.name+'"].paramValue, out id)) return "'+ CASE WHEN g.[value] IS NULL THEN a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'不能为空";' WHEN b.name='bigint' THEN 'if (!dic.ContainsKey("'+a.name+'") || !long.TryParse(dic["'+a.name+'"].paramValue, out longid)) return "'+ CASE WHEN g.[value] IS NULL THEN a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'不能为空";' WHEN b.name='decimal' THEN 'if (!dic.ContainsKey("'+a.name+'") || !decimal.TryParse(dic["'+a.name+'"].paramValue, out decimalid)) return "'+ CASE WHEN g.[value] IS NULL THEN a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'不能为空";' ELSE 'if (!dic.ContainsKey("'+a.name+'")) return "'+ CASE WHEN g.[value] IS NULL THEN a.name ELSE ISNULL( CAST( g.[value] AS VARCHAR(max)),'')END +'不能为空";' END AS [C#检查] 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 --and d.name in('Doc_VaseAudit','ValveFeeDetail','ValveFee','LogTrade','CompBalance','ValveFeeConfig') --如果只查询指定表,加上此条件 --order by a.id,a.colorder ORDER BY a.id,a.colorder -----------------------------------------------------------------------------------------------\ --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 ----and d.name 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://jingyan.baidu.com/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',@level2name='%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,@level2name=null ----PowerDesigner导出C#实体类（带备注）---------https://www.cnblogs.com/shuai7boy/p/10219963.html---------- --.if (%isGenerated%) and (%isValidAttribute%) --/// <summary> --/// [%Name% ] --/// </summary> --[%oidDocTag% ] --[%customAttributes% ] -- .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 --A.name AS table_name, --B.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 A.name = '表名' ----------SqlServer数据库表生成C# Model实体类SQL语句------------- -- 使用临时表（遍历表名称，进行生成model -- 创建临时表 IF OBJECT_ID('tempdb.dbo.#tempTable','U') IS NOT NULL DROP TABLE dbo.#tempTable; GO --SELECT * FROM sys.tables ---遍历表名称 SELECT * INTO dbo.#tempTable FROM sys.tables WHERE 1=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 1 SELECT TOP 1 @TableName= name FROM dbo.#tempTable; --逻辑处理begin SET @Result+= ' /// <summary> /// ' + @TableName + ' /// </summary> public class ' + @TableName + ' {' select @Result = @Result + ' /// <summary> /// ' + CONVERT(NVARCHAR(500), ISNULL(ColName, '无')) + ' /// </summary> public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; } ' from ( SELECT replace(col.name, ' ', '_') ColumnName, column_id ColumnId, prop.value ColName, case typ.name 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_' + typ.name end ColumnType, case when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') then '?' else '' end NullableSign from sys.columns col join sys.types typ on col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id LEFT JOIN sys.extended_properties prop ON col.object_id = prop.major_id AND col.column_id = prop.minor_id where object_id = object_id(@TableName) ) t --order by ColumnId set @Result = @Result + ' }' --逻辑处理end DELETE FROM dbo.#tempTable WHERE name=@TableName; --删除临时表中已处理数据 END SELECT @Result print @Result
C#将集合若干组（分页）
/// <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; }