SQL快捷键设置

--查询存储过程代码
Ctrl+F1        sp_helptext


--查询倒序100行
Ctrl+3        sp_executesql N'DECLARE @tempsql nvarchar(1024); declare @temp NVARCHAR(20);set @tempsql = N''SELECT TOP 1 @temp = a.name from syscolumns a(nolock), sysobjects b(nolock) where a.id=b.id and b.name=''''''+@tablename +''''''ORDER BY a.colid ;'';exec sp_executesql @tempsql,N''@temp NVARCHAR(20) output'',@temp OUTPUT ; IF OBJECT_ID(@tablename) IS NOT NULL  BEGIN TRY EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename +''   ORDER BY    ''+@tablename+''Id  DESC'' ) END  TRY BEGIN CATCH EXEC(N''SELECT TOP(''+@n+N'') * FROM ''+@tablename +''   ORDER BY    ''+@temp+''  DESC'') END CATCH ',N'@tablename nvarchar(100),@n int=100 ',

--查询表说明
Ctrl+4        sp_helpremark 


--关键字搜索存储过程名字
Ctrl+5        sp_executesql N'SELECT * FROM sys.objects WHERE type IN (''U'',''V'',''P'',''FN'') AND name LIKE ''%''+@keyword+''%'' ORDER BY type,name',N'@keyword nvarchar(50)',

--添加表说明
Ctrl+6         sp_executesql N'DECLARE @tempName NVARCHAR(100) ;DECLARE @tempTable NVARCHAR(100) ;SELECT @tempName =  STUFF(@tempTxt,1,charindex(''Q'',''''+@tempTxt +''''),'''');SELECT @tempTable =  SUBSTRING(@tempTxt,1,charindex(''Q'',''''+@tempTxt +'''')-1);IF @tempTxt<> ''''  BEGIN   EXECUTE sp_addextendedproperty N''MS_Description'', @tempName, N''user'', N''dbo'', N''table'', @tempTable;EXEC sp_helpremark @tempTable;END',N'@tempTxt nvarchar(200)',

--存储过程搜索
Ctrl+7        sp_executesql N'SELECT DISTINCT name from sysobjects o, syscomments s where o.id = s.id and text like ''%''+@keyword+''%'' and o.xtype = ''P''',N'@keyword nvarchar(50)',

sp_helpremark 代码：

CREATE PROC [dbo].[sp_helpremark]              
              
@TABLE_NAME VARCHAR(50)              
              
AS              
             
------sqlserver 查询某个表的列名称、说明、备注、类型等   
SELECT      表名       = case when a.colorder=1 then d.name else '' end,  
       表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,       
    字段序号   = a.colorder,       
    字段名     = a.name,     
    字段说明   = isnull(g.[value],''),    
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,       
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else 
'' end,       
    类型       = b.name,       
    占用字节数 = a.length,       
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),       
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),       
    允许空     = case WHEN a.isnullable=1 then '√'else '' end,       
 默认值     = isnull(e.text,''),       
 字段说明   = isnull(g.[value],'') FROM syscolumns a left join systypes b on a.xusertype=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_propertie
s g on a.id=G.major_id and a.colid=g.minor_id   left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where 1=1  and  d.name =@TABLE_NAME order by a.id,a.colorder    
        
--SELECT           
-- (case when a.colorder=1 then ddd.value else '' end) as "表名（中文）",--如果表名相同就返回空          
-- (case when a.colorder=1 then d.name else '' end) as 表名,--如果表名相同就返回空          
-- (case when a.colorder=1 then ddd.value else '' end) as 表说明,--如果表名相同就返回空          
--     a.colorder as        
        
-- 字段序号,          
--     a.name as 字段名,          
--  isnull(g.[value],'') AS 字段说明,           
--     (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) as 是否自增标识,          
--     (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) as 主键,--查询主键END          
--b.name as 类型,          
-- a.length as 占用字节数,          
-- COLUMNPROPERTY(a.id,a.name,'PRECISION') as  长度,          
-- isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,          
-- (case when a.isnullable=1 then '√'else '' end) as 允许空,          
-- isnull(e.text,'') 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 OUTER JOIN( SELECT        
        
-- major_id, value         
--     FROM sys.extended_properties         
--     WHERE name='MS_Description' AND minor_id = 0)        
--    as ddd  ON a.id = ddd.major_id        
-- 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  where d.name = ''+@TABLE_NAME+''        
-- order by a.id,a.colorder  

--查询嵌套存储过程
SELECT  * FROM (SELECT  NAME AS ProcedureName, SUBSTRING(( SELECT  ', ' + OBJDEP.NAME
FROM    sysdepends
        INNER JOIN sys.objects OBJ ON sysdepends.ID = OBJ.OBJECT_ID
        INNER JOIN sys.objects OBJDEP ON sysdepends.DEPID = OBJDEP.OBJECT_ID
WHERE obj.type = 'P'
AND Objdep.type = 'P'
AND sysdepends.id = procs.object_id
ORDER BY OBJ.name

FOR
XML PATH('')
), 2, 8000) AS NestedProcedures
FROM sys.procedures  procs )InnerTab
WHERE NestedProcedures IS NOT NULL