这里有一个简单但有用的例子:

手动编辑表内容时，可以通过输入Control-0在列中插入NULL。

我用来将这个存储过程添加到主数据库，

改进:

修剪主机名，这样复制粘贴就可以在VNC上工作。 增加了一个LOCK选项，用于查看当前锁定的进程。

用法:

EXEC sp_who3 'ACTIVE' EXEC sp_who3 'LOCK' 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

行构造函数

您可以用一个插入语句插入多行数据。

INSERT INTO Colors (id, Color)
VALUES (1, 'Red'),
       (2, 'Blue'),
       (3, 'Green'),
       (4, 'Yellow')

SQL Server Management Studio键盘快捷键…这将使日常工作的结果越来越快。http://sqlserver-qa.net/blogs/tools/archive/2007/04/25/management-studio-shortcut-keys.aspx

Stored proc 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。该过程使用位掩码来破译您想要返回的内容。

本周我学到的最令人惊讶的事情是在ORDER By子句中使用CASE语句。例如:

declare @orderby varchar(10)

set @orderby = 'NAME'

select * 
    from Users
    ORDER BY 
        CASE @orderby
            WHEN 'NAME' THEN LastName
            WHEN 'EMAIL' THEN EmailAddress
        END