SQL快捷键设置

 1 --查询存储过程代码
 2 Ctrl+F1        sp_helptext
 3 
 4 
 5 --查询倒序100行
 6 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 ‘,
 7 
 8 --查询表说明
 9 Ctrl+4        sp_helpremark 
10 
11 
12 --关键字搜索存储过程名字
13 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)‘,
14 
15 --添加表说明
16 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)‘,
17 
18 --存储过程搜索
19 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)‘,

 1 CREATE PROC [dbo].[sp_helpremark]              
 2               
 3 @TABLE_NAME VARCHAR(50)              
 4               
 5 AS              
 6              
 7 ------sqlserver 查询某个表的列名称、说明、备注、类型等   
 8 SELECT      表名       = case when a.colorder=1 then d.name else ‘‘ end,  
 9        表说明     = case when a.colorder=1 then isnull(f.value,‘‘) else ‘‘ end,       
10     字段序号   = a.colorder,       
11     字段名     = a.name,     
12     字段说明   = isnull(g.[value],‘‘),    
13     标识       = case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end,       
14     主键       = 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 
15 ‘‘ end,       
16     类型       = b.name,       
17     占用字节数 = a.length,       
18     长度       = COLUMNPROPERTY(a.id,a.name,‘PRECISION‘),       
19     小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,‘Scale‘),0),       
20     允许空     = case WHEN a.isnullable=1 then ‘√‘else ‘‘ end,       
21  默认值     = isnull(e.text,‘‘),       
22  字段说明   = 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
23 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    
24         
25 --SELECT           
26 -- (case when a.colorder=1 then ddd.value else ‘‘ end) as "表名（中文）",--如果表名相同就返回空          
27 -- (case when a.colorder=1 then d.name else ‘‘ end) as 表名,--如果表名相同就返回空          
28 -- (case when a.colorder=1 then ddd.value else ‘‘ end) as 表说明,--如果表名相同就返回空          
29 --     a.colorder as        
30         
31 -- 字段序号,          
32 --     a.name as 字段名,          
33 --  isnull(g.[value],‘‘) AS 字段说明,           
34 --     (case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end) as 是否自增标识,          
35 --     (case when (SELECT count(*) FROM sysobjects--查询主键          
36 --                     WHERE (name in          
37 --                             (SELECT name FROM sysindexes           
38 --                               WHERE (id = a.id)  AND (indid in          
39 --                                     (SELECT indid FROM sysindexkeys          
40 --                                       WHERE (id = a.id) AND (colid in          
41                            
42         
43 --                      (SELECT colid FROM syscolumns          
44 --                                          WHERE (id = a.id) AND (name = a.name)        
45 --           )          
46 --                                      )        
47 --                    )        
48 --        ))        
49 --      )        
50 --         AND         
51 --(xtype = ‘PK‘))>0 then ‘√‘ else ‘‘ end) as 主键,--查询主键END          
52 --b.name as 类型,          
53 -- a.length as 占用字节数,          
54 -- COLUMNPROPERTY(a.id,a.name,‘PRECISION‘) as  长度,          
55 -- isnull(COLUMNPROPERTY(a.id,a.name,‘Scale‘),0) as 小数位数,          
56 -- (case when a.isnullable=1 then ‘√‘else ‘‘ end) as 允许空,          
57 -- isnull(e.text,‘‘) as 默认值          
58 -- FROM syscolumns a         
59          
60 -- left join systypes b        
61          
62 -- on a.xtype=b.xusertype          
63 -- inner join sysobjects d           
64 -- on a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘          
65 -- LEFT OUTER JOIN( SELECT        
66         
67 -- major_id, value         
68 --     FROM sys.extended_properties         
69 --     WHERE name=‘MS_Description‘ AND minor_id = 0)        
70 --    as ddd  ON a.id = ddd.major_id        
71 -- left join syscomments e          
72 -- on a.cdefault=e.id          
73 -- left join sys.extended_properties g          
74 -- on a.id=g.major_id AND a.colid = g.minor_id  where d.name = ‘‘+@TABLE_NAME+‘‘        
75 -- order by a.id,a.colorder