SQL Server的隐藏功能
SQL Server的一些隐藏功能是什么？
例如，未记录的系统存储过程，做一些有用的技巧却没有足够记录的技巧？
答案
感谢大家提供的所有出色答案！
存储过程
- sp_msforeachtable：使用"？"运行命令替换为每个表名(v6.5及更高版本)
- sp_msforeachdb：使用"？"运行命令替换为每个数据库名称(v7及更高版本)
- sp_who2：与sp_who一样，但包含更多有关故障排除块的信息(v7及更高版本)
- sp_helptext：如果需要存储过程的代码，请查看＆UDF
- sp_tables：返回范围内数据库的所有表和视图的列表。
- sp_stored_procedures：返回所有存储过程的列表
- xp_sscanf：将数据从字符串读取到每个格式参数指定的参数位置。
- xp_fixeddrives ::查找具有最大可用空间的固定驱动器
- sp_help：如果我们想了解表的结构，索引和约束。以及视图和UDF。快捷键是Alt + F1
片段
- 以随机顺序返回行
- 上次修改日期的所有数据库用户对象
- 仅返回日期
- 查找哪个日期属于当前星期内的记录。
- 查找上周发生日期的记录。
- 返回当前星期开始的日期。
- 返回上周开始的日期。
- 查看已部署到服务器的过程的文本
- 删除所有与数据库的连接
- 表校验和
- 行校验和
- 将所有过程拖放到数据库中
- 恢复后正确重新映射登录ID
- 从INSERT语句调用存储过程
- 按关键字查找程序
- 将所有过程拖放到数据库中
- 以编程方式查询事务日志中的数据库。
功能
- HashBytes()
- 加密密钥
- PIVOT命令
杂项
- 连接字符串添加
- TableDiff.exe
- 登录事件的触发器(Service Pack 2中的新增功能)
- 通过持久计算列(pcc)来提高性能。
- sys.database_principles中的DEFAULT_SCHEMA设置
- 强制参数化
- 十进制存储格式
- 在几秒钟内找出最受欢迎的查询
- 可扩展的共享数据库
- SQL Management Studio中的表/存储过程筛选器功能
- 跟踪标志
- " GO"重复批处理后的编号
- 使用架构的安全性
- 使用内置的加密功能，视图和带有触发器的基表进行加密
解决方案
以下是一些我认为有用的功能，但很多人似乎并不了解：
sp_tables
Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects.
关联
sp_stored_procedures
Returns a list of stored procedures in the current environment.
关联
HashBytes()返回其输入的MD2，MD4，MD5，SHA或者SHA1哈希。
使用EncryptByKey进行简单加密
对于解析存储过程参数很有用：xp_sscanf
Reads data from the string into the argument locations specified by each format argument. The following example uses xp_sscanf to extract two values from a source string based on their positions in the format of the source string.
DECLARE @filename varchar (20), @message varchar (20) EXEC xp_sscanf 'sync -b -fproducts10.tmp -rrandom', 'sync -b -f%s -r%s', @filename OUTPUT, @message OUTPUT SELECT @filename, @message
Here is the result set.
-------------------- -------------------- products10.tmp random
sp_who2，与sp_who一样，但包含更多有关故障排除块的信息
/ 找到具有最大可用空间的固定驱动器，也可以复制文件以估计哪个磁盘最快/
EXEC master..xp_fixeddrives
/ 在使用或者引用之前检查有关文件的假设/
EXEC master..xp_fileexist 'C:\file_you_want_to_check'
此处有更多详细信息
sp_msforeachtable：用'？'运行命令替换为每个表名。
exec sp_msforeachtable "dbcc dbreindex('?')"
例如
exec sp_msforeachtable
@Command1 = 'print ''reindexing table ?''',
@Command2 = 'dbcc dbreindex(''?'')',
@Command3 = 'select count (*) [?] from ?'
每个表最多可以发出3条命令
另外，sp_MSforeachdb
-- Return rows in a random order
SELECT
SomeColumn
FROM
SomeTable
ORDER BY
CHECKSUM(NEWID())
一种鲜为人知的TSQL技术，用于以随机顺序返回行：
连接字符串添加功能：
MultipleActiveResultSets = true;
这使得ADO.Net 2.0及更高版本可以在单个数据库连接上读取多个，仅转发和只读的结果集，如果我们要进行大量的读取操作，则可以提高性能。即使我们混合使用查询类型，也可以将其打开。
应用程序名称= MyProgramName
现在，当我们想通过查询sysprocesses表查看活动连接的列表时，程序的名称将显示在program_name列中，而不是" .Net SqlClient数据提供程序"
select name, modify_date,
case when type_desc = 'USER_TABLE' then 'Table'
when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION') then 'Function'
end as type_desc
from sys.objects
where type in ('U', 'P', 'FN', 'IF', 'TF')
and is_ms_shipped = 0
order by 2 desc
这是我写的查询，以按上次修改日期列出所有数据库用户对象：
表/存储过程过滤器功能是一个半隐藏功能，非常有用。
在SQL Server Management Studio对象资源管理器中，右键单击"表"或者"存储过程"文件夹，选择"筛选器"菜单，然后选择"筛选器设置"，然后在"名称包含"行中输入部分名称。
同样，使用"删除过滤器"再次查看所有表/存储过程。
Select Cast(Floor(Cast(Getdate() As Float))As Datetime)
仅返回日期
Select DateAdd(Day, 0, DateDiff(Day, 0, Getdate()))
或者
where dateadd( week, datediff( week, 0, TransDate ), 0 ) = dateadd( week, datediff( week, 0, getdate() ), 0 )
查找哪个日期属于当前星期内的记录。
where dateadd( week, datediff( week, 0, TransDate ), 0 ) = dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )
查找上周发生日期的记录。
select dateadd( week, datediff( week, 0, getdate() ), 0 )
返回当前星期开始的日期。
select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )
返回上周开始的日期。
Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
删除所有与数据库的连接：
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)
表校验和
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) Where Column = Value
行校验和
select * from sys.dm_os_performance_counters select * from sys.dm_exec_requests
对于SQL Server 2005：
- 使用表差异工具，我们可以发现和协调源表和目标表或者视图之间的差异。 Tablediff Utility可以报告架构和数据的差异。 tablediff最受欢迎的功能是它可以生成可以在目标上运行的脚本，该脚本可以协调表之间的差异。
TableDiff.exe
关联
DECLARE @procedureName NVARCHAR( MAX ), @procedureText NVARCHAR( MAX )
SET @procedureName = 'myproc_Proc1'
SET @procedureText = (
SELECT OBJECT_DEFINITION( object_id )
FROM sys.procedures
WHERE Name = @procedureName
)
PRINT @procedureText
我发现这个小脚本非常方便，可以查看已部署到服务器的过程的文本：
SELECT IDENTITY ( int, 1, 1 ) id,
[name]
INTO #tmp
FROM sys.procedures
WHERE [type] = 'P'
AND is_ms_shipped = 0
DECLARE @i INT
SELECT @i = COUNT( id ) FROM #tmp
WHILE @i > 0
BEGIN
DECLARE @name VARCHAR( 100 )
SELECT @name = name FROM #tmp WHERE id = @i
EXEC ( 'DROP PROCEDURE ' + @name )
SET @i = @i-1
END
DROP TABLE #tmp
如果我们想将所有过程拖放到数据库中-
sp_helptext 'ProcedureName'
如果我们需要存储过程的代码，则可以：
(不确定它是否为隐藏功能，但我一直都在使用它)
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
在出于测试目的或者其他目的还原数据库时很有用。正确地重新映射登录ID：
与其说是隐藏功能，不如说是在Management Studio中的Tools \ Options \ Keyboard下设置键映射：
Alt + F1默认为sp_help"选定的文本"，但是如果不为sp_helptext"选定的文本"添加Ctrl + F1，我将无法生存
跟踪标志！在SQL Server 2000的死锁调试中，" 1204"是非常宝贵的(2005年对此有更好的工具)。
CREATE TABLE #toto (v1 int, v2 int, v3 char(4), status char(6)) INSERT #toto (v1, v2, v3, status) EXEC dbo.sp_fulubulu(sp_param1) SELECT * FROM #toto DROP TABLE #toto
存储过程的诀窍是可以从INSERT语句调用它们。在使用SQL Server数据库时，我发现这非常有用。
我最喜欢的是master..xp_cmdshell。它允许我们从服务器上的命令提示符运行命令并查看输出。如果我们无法登录服务器，则非常有用，但是我们需要获取信息或者以某种方式对其进行控制。
- master..xp_cmdshell'dir c：'
例如，列出运行SQL Server的服务器的C：驱动器上的文件夹。
- master..xp_cmdshell'sc查询"我的服务"'
- master..xp_cmdshell'sc stop"我的服务"'
- master..xp_cmdshell'sc启动"我的服务"'
我们也可以启动和停止服务。
它非常强大，但是也存在安全风险。许多人禁用它是因为它很容易被使用在服务器上做不好的事情。但是，如果我们可以使用它，那么它将非常有用。
sp_help 'TableName'
如果我们想了解表的结构，索引和约束：
CAST(CONVERT(varchar,getdate(),103) as datetime)
@Gatekiller当然可以更轻松地获取日期
如果我们在区域设置中未使用DD / MM / YYYY，则需要使用不同于103的值。SQL Books Online中的Lookup CONVERT函数用于区域设置代码。
PRINT 'X' GO 10
在Management Studio中，我们可以在GO批处理结束标记后放置一个数字，以使批处理重复该次数：
将打印" X"十次。在进行重复性操作时，这可以避免繁琐的复制/粘贴操作。
- 登录触发器可以帮助补充审核和合规性。例如，登录事件可用于强制执行连接规则(例如，通过特定用户名限制连接或者通过用户名将连接限制到特定时间段)或者仅用于跟踪和记录常规连接活动。就像在任何触发器中一样，ROLLBACK取消正在执行的操作。在登录事件的情况下，这意味着取消连接建立。当以最小配置模式启动服务器或者通过专用管理员连接(DAC)建立连接时，登录事件不会触发。
登录事件的触发器
关联
- 计算列可以将运行时计算成本转移到数据修改阶段。计算列与行的其余部分一起存储，并且当计算列上的表达式与查询匹配时将透明地使用计算列。我们还可以在PCC上建立索引，以加快对表达式的筛选和范围扫描。
持久计算列
关联
- SQL Server通过名称解析提供了极大的灵活性。但是，名称解析需要付出一定的代价，并且在无法完全限定对象引用的临时工作负载中会变得非常昂贵。 SQL Server 2005允许为每个数据库原理(也称为用户)设置DEFEAULT_SCHEMA的新设置，这可以消除此开销，而无需更改TSQL代码。
sys.database_principles中的DEFAULT_SCHEMA设置
关联
- 通过参数化，SQL Server可以利用查询计划的重用，并避免后续执行类似查询时的编译和优化开销。但是，由于一个或者另一个原因，有许多应用程序仍然受到临时查询编译开销的困扰。对于那些查询编译数量很多并且降低CPU使用率和响应时间对工作负载至关重要的情况，强制参数化可以提供帮助。
强制参数化
关联
- SQL Server 2005为数字和十进制数据类型添加了一种新的存储格式，称为vardecimal。 Vardecimal是十进制类型的变长表示形式，可以将未使用的字节保存在该行的每个实例中。最大的节省量来自以下情况：十进制定义较大(例如：decimal(38,6))，但是存储的值较小(例如，值0.0)，或者存在大量重复值，或者数据稀疏地填充。
十进制存储格式
关联
- 使用sys.dm_exec_query_stats，我们可以通过单个查询找出查询分析的许多组合。
找出最受欢迎的查询
select * from sys.dm_exec_query_stats order by execution_count desc
关联
与共同体
- 通过可伸缩共享数据库，可以在商品计算机上安装相同的物理驱动器，并允许SQL Server 2005的多个实例处理同一组数据文件。该安装程序不需要为每个SQL Server实例重复存储，并允许通过多个具有自己的本地资源(例如cpu，内存，tempdb以及可能的其他本地数据库)的SQL Server实例进行额外的处理。
可扩展的共享数据库
关联
按关键字查找程序
SELECT OBJECT_NAME(ID) FROM SysComments WHERE Text LIKE '%SearchString%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1
哪些过程包含特定文本(表名，列名，变量名，TODO等)？
我知道它不是完全隐藏的，但是没有太多人知道PIVOT命令。我能够更改使用游标的存储过程，并花了2分钟时间来运行快速的6秒代码，这是行数的十分之一！
这是我今天学到的，因为我需要搜索一笔交易。
USE mydatabase; SELECT * FROM ::fn_dblog(NULL, NULL)
:: fn_dblog
这使我们可以查询事务日志中的数据库。
http://killspid.blogspot.com/2006/07/using-fndblog.html
我最喜欢的一些东西：
在sp2脚本选项/工具/选项/脚本中添加
使用架构的新安全性创建两个架构：user_access，admin_access。将用户proc放在一个，将admin proc放在另一个，例如：user_access.showList，admin_access.deleteUser。将架构上的EXECUTE授予应用用户/角色。不再需要一直执行授予权限。
使用内置的加密功能，视图(用于演示以进行解密)和带有触发器的基表(用于在插入/更新时进行加密)进行加密。
好，这是我的2美分：
http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/
我懒得在这里重写整个内容，所以请检查我的帖子。对于许多人来说，这可能是微不足道的，但有些人会发现它是"隐藏的宝石"。
编辑：
SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE], CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE] FROM ADVENTUREWORKS.SYS.OBJECTS AS T JOIN ADVENTUREWORKS.SYS.COLUMNS AS C ON T.OBJECT_ID=C.OBJECT_ID JOIN ADVENTUREWORKS.SYS.TYPES AS P ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID WHERE T.TYPE_DESC=‘USER_TABLE’;
一段时间后，我决定在此处添加代码，因此我们不必跳到我的博客即可查看代码。
DECLARE @tablename VARCHAR(60) DECLARE cursor_tablenames CURSOR FOR SELECT name FROM AdventureWorks.sys.tables OPEN cursor_tablenames FETCH NEXT FROM cursor_tablenames INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN SELECT t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE], CAST(p.PRECISION AS VARCHAR) +‘/’+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale] FROM AdventureWorks.sys.objects AS t JOIN AdventureWorks.sys.columns AS c ON t.OBJECT_ID=c.OBJECT_ID JOIN AdventureWorks.sys.types AS p ON c.system_type_id=p.system_type_id WHERE t.name = @tablename AND t.type_desc=‘USER_TABLE’ ORDER BY t.name ASC FETCH NEXT FROM cursor_tablenames INTO @tablename END CLOSE cursor_tablenames DEALLOCATE cursor_tablenames
或者，如果要完全拉出所有用户表，请使用CURSOR，如下所示：
sp_executesql
其他参考资料(我的博客)：http://dbalink.wordpress.com/2009/01/21/how-to-create-cursor-in-tsql/
用于执行字符串中的语句。与Execute一样好，但可以返回参数
由于我是程序员，而不是DBA，所以我最喜欢的隐藏功能是SMO库。从数据库/表/列的创建和删除到脚本编写到备份和还原，SQL Server中的几乎所有内容都可以实现自动化。如果可以在SQL Server Management Studio中执行此操作，则可以在SMO中将其自动化。
没有记录
RowNumber由Itzik Ben-Gan提供
http://www.sqlmag.com/article/articleid/97675/sql_server_blog_97675.html
将XACT_ABORT设置为ON
在发生错误时回滚所有事务
所有sp_都很有用，只需在线浏览书籍即可
我在Management Studio中一直使用的键盘快捷键
F6在结果和查询之间切换
Alt + X或者F5运行查询中的选定文本(如果未选择任何内容)将运行整个窗口
Alt + T和Alt + D分别以文本或者网格形式显示
exec sp_depends 'fn_myFunction'
我发现sp_depends很有用。它显示依赖于给定对象的对象，例如
返回依赖于此功能的对象(请注意，如果对象最初没有以正确的顺序运行到数据库中，则将给出错误的结果。)
例外与相交
这两个关键字不是编写复杂的联接和子查询，而是比较两个查询结果时表达查询意图的一种更简洁，更易读的方式。作为SQL Server 2005的新功能，它们对UNION进行了强有力的补充，UNION在TSQL语言中已经存在多年了。
EXCEPT，INTERSECT和UNION的概念是集合论的基础，是所有现代RDBMS使用的关系建模的基础和基础。现在，使用TSQL可以更直观，更轻松地生成维恩图类型结果。
exec sp_configure 'show advanced options', 1; reconfigure; go exec sp_configure 'blocked process threshold', 30; reconfigure;
在SQL Server 2005中，我们不再需要运行sp-blocker-pss80存储过程。相反，我们可以执行以下操作：
然后，我们可以启动SQL跟踪，并在"错误和警告"组中选择"阻止的流程报告"事件类。该事件的详细信息在这里。
declare @orderby varchar(10)
set @orderby = 'NAME'
select *
from Users
ORDER BY
CASE @orderby
WHEN 'NAME' THEN LastName
WHEN 'EMAIL' THEN EmailAddress
END
我这周学到的最令人惊讶的事情涉及在ORDER By子句中使用CASE语句。例如：
基于加固的数据库开发人员对它的强烈反应，CLR集成将在那里排名靠前。 =)
这里有一些未公开的文件：未公开但方便的SQL Server Procs和DBCC命令
SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
GrandTotal, CustomerId, PurchaseDate
FROM Orders
在SQL Server 2005/2008中，以在SELECT查询结果中显示行号：
use db go DECLARE @procName varchar(100) DECLARE @cursorProcNames CURSOR SET @cursorProcNames = CURSOR FOR select name from sys.procedures where modify_date > '2009-02-05 13:12:15.273' order by modify_date desc OPEN @cursorProcNames FETCH NEXT FROM @cursorProcNames INTO @procName WHILE @@FETCH_STATUS = 0 BEGIN -- see the text of the last stored procedures modified on -- the db , hint Ctrl + T would give you the procedures test set nocount off; exec sp_HelpText @procName --- or print them -- print @procName FETCH NEXT FROM @cursorProcNames INTO @procName END CLOSE @cursorProcNames select @@error
use db
go
select o.name
, (SELECT [definition] AS [text()]
FROM sys.all_sql_modules
WHERE sys.all_sql_modules.object_id=a.object_id
FOR XML PATH(''), TYPE
) AS Statement_Text
, a.object_id
, o.modify_date
FROM sys.all_sql_modules a
LEFT JOIN sys.objects o ON a.object_id=o.object_id
ORDER BY 4 desc
--select * from sys.objects
ORDER BY是强制性条款。 OVER()子句告诉SQL Engine对指定列(在本例中为OrderId)中的数据进行排序，并根据排序结果分配数字。
获取垂直格式的列标题列表：
在网格结果中复制列名称
工具选项查询结果SQL Server结果网格化
勾选"复制或者保存结果时包括列标题"
我们此时需要建立新的连接，然后运行查询
现在，当我们从网格中复制结果时，我们将获得列标题
还
如果我们然后将结果复制到excel
仅复制列标题
特殊粘贴(不得与复印区域重叠)
勾选"移调"
好的
[我们可能希望添加一个"，"并在此时自动填充]
我们有垂直格式的即时列列表
我不确定这是否是隐藏功能，但我偶然发现了这一点，并发现它在许多情况下都很有用。我们可以在单个select语句中合并一组字段，而不是使用游标并遍历select语句。
DECLARE @nvcConcatonated nvarchar(max) SET @nvcConcatonated = '' SELECT @nvcConcatonated = @nvcConcatonated + C.CompanyName + ', ' FROM tblCompany C WHERE C.CompanyID IN (1,2,3) SELECT @nvcConcatonated
例子：
Acme, Microsoft, Apple,
结果：
根据单个状态中的管道分隔的ID字符串恢复结果(替代传递xml或者首先将分隔的字符串转换为表)
DECLARE @nvcIDs nvarchar(max) SET @nvcIDs = '|1|2|3|' SELECT C.* FROM tblCompany C WHERE @nvcIDs LIKE '%|' + CAST(C.CompanyID as nvarchar) + '|%'
例子：
INSERT INTO someTable EXEC sp_someproc
执行存储的proc并将结果捕获到(temp)表中以进行进一步处理，例如：
CREATE TABLE #dbs
(
name nvarchar(50),
db_size nvarchar(50),
owner nvarchar(50),
dbid int,
created datetime,
status nvarchar(255),
compatiblity_level int
)
INSERT INTO #dbs EXEC sp_helpdb
SELECT * FROM #dbs
ORDER BY CONVERT(decimal, LTRIM(LEFT(db_size, LEN(db_size)-3))) DESC
DROP TABLE #dbs
SQLCMD
示例：显示" sp_help"输出，但按数据库大小排序：
如果我们具有一遍又一遍地运行的脚本，但是必须更改一些细微的细节，那么以sqlcmd模式运行ssms真是棒极了。 sqlcmd命令行也很漂亮。
- 我们可以设置变量。不需要跳过sp_exec箍的适当变量
- 我们可以一个接一个地运行多个脚本
- 这些脚本可以引用"外部"脚本中的变量
我最喜欢的功能是：
Red Gate的Simpletalk并没有再大肆宣传，而是对sqlcmd SQLCMD Workbench进行了出色的总结。 Donabel Santos也有一些很棒的SQLCMD示例。
我用来将此存储过程添加到主数据库中，
- 修剪主机名，因此复制粘贴可在VNC上使用。
- 添加了一个LOCK选项，用于仅查看当前已锁定的进程。
改进之处：
- EXEC sp_who3'有效的'
- EXEC sp_who3'锁定'
- EXEC sp_who3 spid_No
用法：
CREATE procedure sp_who3
@loginame sysname = NULL --or 'active' or 'lock'
as
declare @spidlow int,
@spidhigh int,
@spid int,
@sid varbinary(85)
select @spidlow = 0
,@spidhigh = 32767
if @loginame is not NULL begin
if upper(@loginame) = 'ACTIVE' begin
select spid, ecid, status
, loginame=rtrim(loginame)
, hostname=rtrim(hostname)
, blk=convert(char(5),blocked)
, dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh AND
upper(cmd) <> 'AWAITING COMMAND'
return (0)
end
if upper(@loginame) = 'LOCK' begin
select spid , ecid, status
, loginame=rtrim(loginame)
, hostname=rtrim(hostname)
, blk=convert(char(5),blocked)
, dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where spid >= 0 and spid <= 32767 AND
upper(cmd) <> 'AWAITING COMMAND'
AND convert(char(5),blocked) > 0
return (0)
end
end
if (@loginame is not NULL
AND upper(@loginame) <> 'ACTIVE'
)
begin
if (@loginame like '[0-9]%') -- is a spid.
begin
select @spid = convert(int, @loginame)
select spid, ecid, status
, loginame=rtrim(loginame)
, hostname=rtrim(hostname)
, blk=convert(char(5),blocked)
, dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where spid = @spid
end
else
begin
select @sid = suser_sid(@loginame)
if (@sid is null)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
select spid, ecid, status
, loginame=rtrim(loginame)
, hostname=rtrim(hostname)
, blk=convert(char(5),blocked)
, dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where sid = @sid
end
return (0)
end
/* loginame arg is null */
select spid,
ecid,
status
, loginame=rtrim(loginame)
, hostname=rtrim(hostname)
, blk=convert(char(5),blocked)
, dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh
return (0) -- sp_who
而已。
好的，这是我剩下的几个人，可惜我错过了开始的机会，但请继续努力，这里有一些顶级的东西！
- Alt + F1在所选文本上执行sp_help
- Alt-D-专注于数据库下拉列表，因此我们可以使用带有字母光标键的select db。
查询分析器
- 如果(object_id(" nameofobject")IS NOT NULL)开始<做某事>结束-最简单的存在检查
- sp_locks-深度锁定信息比sp_who2(呼叫的第一个端口)多
- dbcc inputbuffer(spid)-正在执行的进程的顶行列表(有点有用，但简短)
- dbcc outputbuffer(spid)-执行过程的输出的第一行列表
T语言
- 大量使用子查询时，可以自由处理集合中的数据
e.g. to obtain a list of married people over fifty you could select a set of people who are married in a subquery and join with a set of the same people over 50 and output the joined results - please excuse the contrived example
通用T-sql技巧
CTRL-E在查询分析器中执行当前选定的文本。
许多SQL Server开发人员似乎仍然不了解DELETE，INSERT和UPDATE语句上的OUTPUT子句(SQL Server 2005及更高版本)。
DELETE FROM (table) OUTPUT deleted.ID, deleted.Description WHERE (condition)
知道哪些行已被插入，更新或者删除非常有用，并且OUTPUT子句可以很轻松地做到这一点，它允许访问称为"插入"和"删除"的"虚拟"表(如触发器) ：
INSERT INTO MyTable(Field1, Field2) OUTPUT inserted.ID VALUES (Value1, Value2)
如果要将值插入具有INT IDENTITY主键字段的表中，并带有OUTPUT子句，则可以立即获取插入的新ID：
UPDATE (table) SET field1 = value1, field2 = value2 OUTPUT inserted.ID, deleted.field1, inserted.field1 WHERE (condition)
而且，如果我们要进行更新，了解这种情况下的更改可能非常有用，" inserted"表示新值(在UPDATE之后)，而" deleted"则指的是UPDATE之前的旧值：
如果将返回很多信息，则OUTPUT的输出也可以重定向到临时表或者表变量(" OUTPUT INTO @myInfoTable")。
非常有用，鲜为人知！
马克
使用osql实用程序运行命令行查询/脚本/批处理
批量分离器
大多数人不知道，但是" GO"不是SQL命令。它是客户端工具使用的默认批处理分隔符。我们可以在联机丛书中找到有关它的更多信息。
我们可以通过选择Management Studio中的"工具"->"选项"，然后在"查询执行"部分中更改"批处理分隔符"来更改批处理分隔符。
我不知道为什么除了恶作剧之外还想这样做，但这是一件有趣的琐事。
BCP_IN和BCP_OUT非常适合批量数据导入和导出
select row_number() over (order by (select 1)), * from dbo.Table as t
有时候，没有合适的列可以作为排序依据，或者我们只希望表上的默认排序顺序，而我们想枚举每一行。为此，我们可以在" order by"子句中放入"(select 1)"，我们将得到所需的内容。整洁，是吗？
dm_db_index_usage_stats
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'MyDatabase')
AND OBJECT_ID=OBJECT_ID('MyTable')
这样，即使表上没有DateUpdated列，我们也可以知道表中的数据是否最近已更新。
来自以下网址的代码：http：//blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/
引用的信息：
SQL Server表的最后插入行的日期/时间是什么？
在SQL 2005及更高版本中可用
- 在对象资源管理器中，展开给定表下的节点(这样我们将看到列，键，约束，触发器等的文件夹)
- 指向"列"文件夹，然后拖动到查询中。
在Management Studio中，我们可以通过以下方式快速获取表的列的逗号分隔列表：
当我们不想使用通过右键单击表并选择"脚本表为..."，然后选择"插入到..."而返回的令人讨厌的格式时，此方法非常方便。文件夹中包含的逗号分隔名称列表。
ALTER USER wacom_app WITH LOGIN = wacom_app
替代Kolten的sp_change_users_login：
行构造器
INSERT INTO Colors (id, Color)
VALUES (1, 'Red'),
(2, 'Blue'),
(3, 'Green'),
(4, 'Yellow')
我们可以使用单个插入语句插入多行数据。
在SQL Server Management Studio(SSMS)中，可以在对象资源管理器中突出显示对象名称，然后按Ctrl-C将名称复制到剪贴板。
无需按F2或者单击鼠标右键，重命名对象即可复制名称。
我们还可以将对象从"对象资源管理器"拖放到查询窗口中。
这是一个简单但有用的方法：
手动编辑表内容时，可以通过键入Control-0在列中插入NULL。
存储的过程sp_MSdependencies以比sp_depends更有用的方式告诉我们有关对象的依赖性。对于某些生产版本，可以方便地临时禁用子表约束，应用更改然后重新启用子表约束。这是查找依赖于父表的对象的好方法。
create table #deps ( oType int, oObjName sysname, oOwner nvarchar(200), oSequence int ) insert into #deps exec sp_MSdependencies @tableName, null, 1315327 exec sp_MSforeachtable @command1 = 'ALTER TABLE ? NOCHECK CONSTRAINT ALL', @whereand = ' and o.name in (select oObjName from #deps where oType = 8)'
此代码禁用子表约束：
exec sp_MSforeachtable @command1 = 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL', @whereand = ' and o.name in (select oObjName from #deps where oType = 8)'
应用更改后，可以运行以下代码以重新启用约束：
第三个参数称为@flags，它控制将列出哪种依存关系。阅读proc内容，了解如何更改@flags以实现目的。 proc使用位掩码来解密我们要返回的内容。
select * from information_schema
使用
列出SQL Server中的所有数据库，基本表，SPS，视图等。
这些是我喜欢的一些SQL Management Studio隐藏功能。
我喜欢的事情是，如果在突出显示信息时按住ALT键，则可以选择列信息，而不仅仅是整行。
在SQL Management Studio中，我们具有预定义的键盘快捷键：
Ctrl + 1运行sp_who
Ctrl + 2运行sp_lock
Alt + F1运行sp_help
Ctrl + F1运行sp_helptext
因此，如果我们在编辑器中突出显示一个表名并按Alt + F1，它将为我们显示表的结构。
段落数量不匹配
