查询表名为tb_menu的所有列名 select name from syscolumns where id=object_id(‘tb_menu‘) 查询表名为tb_menu的所有列名个数select count(name) from syscolumns where id=object_id(‘tb_menu‘) 或者select count(syscolumns.name) from syscolumns ,sysobjects where syscolumns.id=sysobjects.id and sysobjects.name = ‘tb_menu‘SqlServer查询表中各列名称、表中列数标签：本文系统来源：http://www.cnb...
代码示例： USE teachingGOSELECT *FROM dbo.teach_classORDER BY classno DESCSELECT *FROM dbo.courseORDER BY courseno ASC 主要思路是运用多条语句同时运行 SQL Server多表同时查询标签：本文系统来源：http://www.cnblogs.com/Canace22/p/5468085.html
用的是数据库sqlserver2000,唉，有点老了，好处是到处都有，安装方便。 select ( select count(*) from temp_gzsphzb as t1 where spid <=t2.spid) as xh ,* from temp_gzsphzb as t2order by 1 留做纪念！sqlserver2000 在查询时产生序号列的办法标签：本文系统来源：http://www.cnblogs.com/alasunny/p/5478310.html
DB2分页查询 SELECT * FROM (Select 字段1,字段2,字段3,rownumber() over(ORDER BY 排序用的列名 ASC) AS rn from 表名) AS a1 WHERE a1.rn BETWEEN 10 AND 20 以上表示提取第10到20的纪录 select * from (select rownumber() over(order by id asc ) as rowid from table where rowid <=endIndex ) where rowid > startIndex 如果Order By 的字段有重复的值，那一定要把此字段放到 over()中 select * from ( select ROW_NUMBER() ...
sqlserver 查询数据库大小的方法**/--总部要求每一个月，获取一次ERP数据库增长大小。 --原文出处：http://www.jb51.net/article/30993.htm SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [LogicalName],[Physical_Name] AS[Physical Name],((size *8) / 1024) AS [Size(MB)],[differential_base_time] AS[Differential Base Time] FROM sys.master_files WHERE DB_NAME(database_id) IN(‘Conductor‘) GO本文出自 ...
[Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid),[User] = nt_username, [Status] = er.status, [Wait] = wait_type, [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2, (CASE WHEN er.statement_end_offset = - 1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset) / 2),[Parent Query] = qt.text, Program = program_name, Host...
DBCC SHOWCONTIG( ‘表名‘ ) 重建索引命令 DBCC DBREINDEX (tblWFProcessInstance, ‘‘ , 90) 对全数据库 exec sp_msforeachtable ‘DBCC DBREINDEX(‘ ‘?‘ ‘)‘ sp_who --看看哪个引起的阻塞，blksp_lock --看看锁住了那个资源id，objid ，select object_name(objid) 得到dbcc inputbuffer(54) -- 看看是那个语句MSSQL 查询表空间标签：本文系统来源：http://www.cnblogs.com/blogs2014/p/5517012.html
这里提取指定符串"A"在字段中的出现次数SQL为：select len(keyword)-len(replace(keyword, ‘A‘, ‘ ‘)) from 表原理：用replace函数将要查找的字符替换为空字符，将替换之间的字符串长度-替换后字符串长度 sql server 查询字符串指定字符出现的次数标签：本文系统来源：http://www.cnblogs.com/liwuyi/p/5525793.html
查看传输队列中的消息 --如果尝试从队列中移除时，列将表明哪里出现了问题 select * from sys.transmission_queue-- 查看Service Broker 激活的存储过程 select * from sys.dm_broker_activated_tasks-- 查看数据库中的每个会话端点。会话端点代表Service Broker 会话的每一端。 -- 会话端点视图state列显示会话的状态 select * from sys.conversation_endpoints----------------------------------------------------------------...
--查询表中某列相同值的最近记录 2 SELECT * FROM TbName a 3 WHERE NOT EXISTS 4 ( 5 SELECT 1 FROM TbName WHERE ColumnName=a.ColumnName and CreateTime>a.CreateTime 6 ) SqlServer查询表中某列相同值的最近记录标签：本文系统来源：http://www.cnblogs.com/qubernet/p/5529797.html
a few columns,,, from dbo.LittleTable lt with(nolock) inner join dbo.BitTable bt with(nolock)on lt.Int_Col1=bt.Int_col1 or lt.Int_Col2=bt.Int_Col4我修改了查询条件，将 or lt.Int_Col2=bt.Int_Col4 条件删除，再次执行，查询结果秒出。 上网Google了下这两个WaitTypeHTMEMO (HT = Hash Table, MEMO = memory) HTBUILD (HT = Hash Table, BUILD= build) This wait type (and the other HT* waits) is when a thread is wa...
1. [代码]查询所有存储过程 跳至 [1] [2] [3] [4] [5] [6] [全屏预览] ?1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18select Pr_Name as [存储过程], [参数]=stuff((select ‘，‘+[Parameter] from ( select Pr.Name as Pr_Name,parameter.name +‘ ‘ +Type.Name + ‘ (‘+convert(varchar(32),parameter.max_length)+‘)‘ as Parameter from sys.procedures Pr left join sys.parameters parameter on Pr.object_id = par...
今天写个动态脚本，需要把数据库里面包含“USER_"的表删除掉，突然想不起来如何搜索通配字符了，赶紧查查MSDN，整理了下模糊查询的知识点，留着以后查阅用。 LIKE模糊查询的通配符通配符说明示例%包含零个或多个字符的任意字符串。WHERE title LIKE ‘%computer%‘ 将查找在书名中任意位置包含单词 "computer" 的所有书名。_（下划线）任何单个字符。WHERE au_fname LIKE ‘_ean‘ 将查找以 ean结尾的所有 4 个字母的名字（Dean、...
--本周第一天 SELECT DATEADD(Day,1-(DATEPART(Weekday,getdate())+@@DATEFIRST-1)%7,getdate()) --or select dateadd(wk, datediff(wk,0,getdate()), 0) --本周第一天 select dateadd(wk, datediff(wk,0,getdate()), 6) --上月第一天 SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111) --上月最后一天 SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DAT...
Create table BusinessInfoTable (BuniessCode1 varchar(50),BuniessCode2 varchar(50),BuniessCode3 varchar(50),BuniessCode4 varchar(50),BuniessStatus1 tinyint,BuniessStatus2 tinyint,BuniessDateTime1 Datetime,BuniessDateTime2 Datetime,OtherColumn1 varchar(50),OtherColumn2 varchar(50),OtherColumn3 varchar(50) )declare @i int=0 while @i<1000000 begininsert into BusinessInfoTable values (NEWID(),NEWID(),N...