Technical Article

Productivity Keyboard Shortcuts

,

1. Go to Tools >> Options

2. Environment // Keyboard

3.

For CTRL+3 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 3, @DB,

For CTRL+4 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 4, @DB,

For CTRL+5 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 5, @DB,

For CTRL+6 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 6, @DB,

For CTRL+7 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 7, @DB,

For CTRL+8 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 8, @DB,

For CTRL+9 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 9, @DB,

For CTRL+0 enter DECLARE @DB NVARCHAR(500) SET @DB = DB_NAME() EXEC Utils.dbo.Utils 0, @DB,

4. Run script

5. Restart SSMS

Description:

CTRL+3: selects TOP 1000 rows from a selected table or view (highlite table name in text editor and hit shortcut)

CTRL+4: is Showing details of a table or if it's a SP,FN,TR,VW Scripts the object (very good to quickly check table structure or script a procedure into text editor, just highlite and hit shortcut)

CTRL+5: searches for a database object with selected name in it (# char works like % wildcard, lets say you are looking for object starting with 'ab' and ending with 'xyz', type [ab#xyz] select and hit shortcut)

CTRL+6: NOT IMPLEMENTED

CTRL+7: NOT IMPLEMENTED

CTRL+8: searches for table or view with selected column name in it

CTRL+9: listing columns from selected table or view

CTRL+0: looks for an object that contains selected phrase in the objects script (# char works like % wildcard, lets say you are looking for object containing 'ab...xyz' in the script, type [ab#xyz] select and hit shortcut)

These were made based on te default [dbo] schema so feel free to adjust for your own needs. Just wanted to share something i can't work without anymore.

 

PS. Any ideas for 6 and 7?

USE master
GO

CREATE DATABASE Utils
GO

USE Utils
GO
 
--*******************************
--Author: EB
--*******************************
CREATE PROC [dbo].[Utils](@Action int, @DBName NVARCHAR(1000), @Object nvarchar(1000), @AddParam nvarchar(1000) = NULL)      
AS      
BEGIN            

  DECLARE @SQL nvarchar (4000)      
  DECLARE @_Object_ nvarchar(1000)     
      
  SET @Object = REPLACE (@Object, '#','%')   
  SET @AddParam = REPLACE (@AddParam, '#','%')      
  SET @SQL = ''      


----------------------      
  IF @Action = 3      
  BEGIN      
    IF EXISTS(SELECT 1 FROM dbo.sysobjects o WHERE o.name = @Object) AND LEFT(@Object,1) <> '['  
      SET @_Object_ = '['+@Object+']'   
    ELSE
      SET @_Object_ = @Object  
    
    IF @AddParam IS NOT NULL
    BEGIN
      SET @SQL = N'IF EXISTS (SELECT 1 FROM '+@DBName+'.dbo.syscolumns c INNER JOIN '+@DBName+'.dbo.sysobjects o ON o.ID = c.ID WHERE o.name = '''+@Object+''' AND c.Name = '''+REPLACE(@AddParam,'%', '')+''')
                    EXEC sp_executesql N''SELECT TOP 1000 * FROM '+@DBName+'..' + @_Object_ + ' ORDER BY ' +REPLACE(@AddParam,'%', '') + CASE WHEN CHARINDEX('%', @AddParam) > 0 THEN ' DESC ' ELSE ' ASC ' END + '''
                  ELSE
                  BEGIN
                    DECLARE @ColName NVARCHAR(50) = (SELECT c.name FROM '+@DBName+'.dbo.syscolumns c INNER JOIN '+@DBName+'.dbo.sysobjects o ON o.ID = c.ID WHERE o.name = '''+@Object+''' AND c.colorder = 1)
                    DECLARE @SQL NVARCHAR(2000) = ''SELECT TOP 1000 * FROM '+@DBName+'..' + @_Object_ + ' WHERE ''+@ColName+'' = ''''' + @AddParam  +'''''''
                    EXEC sp_executesql @SQL
                  END'
    END
    ELSE
    BEGIN
      SET @SQL = N'IF EXISTS(SELECT * FROM '+@DBName+'.dbo.syscolumns c INNER JOIN '+@DBName+'.dbo.sysobjects o ON o.ID = c.ID WHERE o.name = '''+@Object+''' AND c.Name = ''ID'')
                   EXEC sp_executesql N''SELECT TOP 1000 * FROM '+@DBName+'..' + @_Object_ + ' ORDER BY ID DESC''
                  ELSE 
                    EXEC sp_executesql N''SELECT TOP 1000 * FROM '+@DBName+'..' + @_Object_+''''
    END
    
  END      
        
  ELSE      
--------------      
  IF @Action = 4      
  BEGIN      
  SET @SQL = '
    IF EXISTS(SELECT 1 FROM '+ @DBName +'.dbo.SysObjects WHERE name = '''+@Object+''' AND type = ''U'')
      EXEC '+ @DBName +'..sp_Help '''+@Object+'''     
    ELSE  
    IF NOT EXISTS(SELECT * FROM '+ @DBName +'.dbo.SysObjects WHERE name = ''ScriptObject'')    
      EXEC '+ @DBName +'..sp_HelpText '+@Object+'     
    ELSE   
    BEGIN  
      IF '+ISNULL(@AddParam,'''XXX''')+' IN (''D'',''DEBBUG'')  
        EXEC dbo.ScriptObject ''' + @DBName + ''', ''' + @Object + ''', @Debbug = 1  
      ELSE  
        EXEC dbo.ScriptObject ''' + @DBName + ''', ''' + @Object + '''
    END '
     
  END      
  ELSE     
--------------      
  IF @Action = 5      
  BEGIN      
      SET @SQL = 'SELECT o.Name, o.Type, o.ID, o.CrDate, o.Parent_Obj AS Parent_ID, ISNULL(op.Name,'''') Parent_Name, ISNULL(op.Type,'''') AS Parent_Type '      
             +'FROM '+ @DBName +'.dbo.sysobjects o '      
             +'LEFT JOIN '+ @DBName +'.dbo.sysobjects op ON op.ID = o.Parent_Obj '      
             +'WHERE o.Name LIKE '       
             + CASE WHEN LEFT(@Object,1) = '%' OR RIGHT(@Object,1)='%'      
                  THEN ''''+@Object+''''       
                  ELSE '''%'+@Object+'%'''       
               END  
             +ISNULL(' AND o.Type = '''+@AddParam+'''' ,'')      
             +' ORDER BY o.Name'      
  END      
  ELSE      
--------------      
       
  IF @Action = 8      
  BEGIN      
    SET @SQL = 'SELECT o.Name ObjectName, o.Type ObjectType, c.ColOrder LP, c.name ColName, '    
              +'  CASE     
                    WHEN t.Name like ''n%char%'' THEN t.name + '' (''+LTRIM(STR(c.Length/2,5))+'')''     
                    WHEN t.Name like ''%char%'' THEN t.name + '' (''+LTRIM(STR(c.Length,5))+'')''     
                    ELSE t.name     
                  END AS Type , '      
              +'  ''@''+c.name ColName_2 , '      
              +'  '','' AS Comma '    
              +'FROM '+ @DBName +'.dbo.syscolumns c '      
              +'INNER JOIN '+ @DBName +'.dbo.sysobjects o ON o.ID = c.ID '      
              +'INNER JOIN '+ @DBName +'.dbo.systypes t ON t.xUserType = c.xUserType '      
              +'WHERE c.name LIKE ''' + @Object + ''''      
              +ISNULL(' AND o.Type = '''+@AddParam+'''' ,'')      
              +' ORDER BY o.Name, c.Number, c.ColOrder '    
  END      
  ELSE      
--------------      
  IF @Action = 9      
  BEGIN      
    SET @SQL = 'SELECT o.Name ObjectName, o.Type ObjectType,  c.ColOrder ColOrder, c.name ColName, '    
              +'  CASE     
                    WHEN t.Name like ''n%char%'' THEN t.name + '' (''+LTRIM(STR(c.Length/2,5))+'')''     
                    WHEN t.Name like ''%char%'' THEN t.name + '' (''+LTRIM(STR(c.Length,5))+'')''     
                    ELSE t.name     
                  END AS Type , '         
              +'  ''@''+c.name ColName_2 , '       
              +'  '','' AS Comma '    
              +'FROM '+ @DBName +'.dbo.syscolumns c  '      
              +'INNER JOIN '+ @DBName +'.dbo.sysobjects o ON o.ID = c.ID  '      
              +'INNER JOIN '+ @DBName +'.dbo.systypes t ON t.xUserType = c.xUserType '      
              +'WHERE o.name LIKE ''' + @Object +''''      
       +ISNULL(' AND o.Type = '''+@AddParam+'''' ,'')      
              +' ORDER BY o.Name, c.Number, c.ColOrder '      
  END      
  ELSE      
      
--------------      
  IF @Action = 0      
  BEGIN      
    SET @SQL = 'SELECT DISTINCT o.Name, o.Type, o.Id 
                FROM '+ @DBName +'.dbo.syscomments t 
                INNER JOIN '+ @DBName +'.dbo.sysobjects o ON o.ID = t.ID '      
              +'WHERE t.TEXT LIKE ''%'+@Object+'%'''

  END      
      
------------------------------------------------------------------------------------      
  PRINT @SQL      
  EXEC sp_executesql @SQL    
    
END 
  
GO

--*******************************
--Author: EB
--*******************************
CREATE PROC  [dbo].[ScriptObject] (@DBName NVARCHAR(1000), @ObjectName varchar(500), @ForceChar10AsLineSeparator bit = 0, @Debbug int = 0) 
AS 
BEGIN  
  SET NOCOUNT ON 
  
   DECLARE @SQL nvarchar(MAX)  
   
   SET @SQL = '
   
    DECLARE @T TABLE (ID int IDENTITY(1,1), ColID int, Def varchar(8000)) 
 
    DECLARE  @Colid int, @ColidPrv int, @Def varchar(8000), @Tmp varchar(8000), @C varchar(2), @C2 varchar(2), @i int, @i2 int 
    DECLARE @ii int 
    DECLARE @DebugTable TABLE (ch varchar(5), i int) 
 
    SET @C = CHAR(13)+CHAR(10) 
    SET @C2 = CHAR(10) 
 
-------
    DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
      SELECT Colid, Text FROM '+@DBName+'.dbo.SysComments s JOIN '+@DBName+'.dbo.SysObjects o ON s.ID = o.ID  
      WHERE o.Name = ''' + @ObjectName +'''
      ORDER BY ColID 
      
    OPEN cur 
    FETCH NEXT FROM cur INTO @Colid, @Def 
 
    WHILE (@@FETCH_STATUS=0) 
    BEGIN 
      SET @Def =  REPLACE(REPLACE(@Def, @C, @c2) , CHAR(13), @c2) 

      INSERT @T 
      SELECT @Colid, Field FROM dbo.CharSeparatedStringToTable(@Def, @c2) 

 
     ----- 
      IF '+CONVERT(VARCHAR(10),@Debbug)+' = 1 
      BEGIN 
        SET @ii = 1 
        WHILE @ii <= LEN(@Def) 
        BEGIN 
          INSERT @DebugTable SELECT SUBSTRING(@Def, @ii,1), ASCII(SUBSTRING(@Def, @ii,1)) 
          SET @ii = @ii+1 
        END 
      END          
     ----- 
        
      SET @ColidPrv = @Colid  
      FETCH NEXT FROM cur INTO @Colid, @Def 
    END 
     
 
    CLOSE cur  
    DEALLOCATE cur  
     
    DECLARE @Cor TABLE(Min int, Max int) 
     
    INSERT @Cor 
    SELECT x1.Max, x2.Min 
    FROM (SELECT ColId, MIN(ID) min, MAX(ID) max FROM @T t1 GROUP BY ColID) x1 
    JOIN (SELECT ColId, MIN(ID) min, MAX(ID) max FROM @T t1 GROUP BY ColID) x2 ON x1.ColId +1 = x2.ColId 
     
    UPDATE t1 
    SET t1.Def = t1.Def + t2.Def 
    FROm @t t1 
    JOIN @Cor c ON t1.ID = c.Min 
    JOIN @T t2 ON t2.ID = c.Max 

    DELETE FROM @t WHERE ID IN(SELECT C.MAX FROM @Cor c) 
  ----
 
    SELECT Def AS [ ] FROM @T ORDER BY ID 
 
    IF '+CONVERT(VARCHAR(10),@Debbug)+' = 1 SELECT *, case when i IN(13,10) THEN ''!'' END AS EOF FROM @DebugTable '
    
    EXEC sp_executesql @SQL
  END 

GO


--*******************************
--Author: EB
--*******************************
CREATE FUNCTION dbo.CharSeparatedStringToTable(@String nvarchar(max), @char nchar)      
RETURNS @Tab TABLE (ID int IDENTITY(1,1), Field nvarchar(max), PRIMARY KEY(ID))      
AS      
BEGIN      
  DECLARE @i int      
  SET @i=1      
        
  IF @String IS NOT NULL      
  BEGIN              
    WHILE 1=1      
    BEGIN           
      IF charindex(@char, @String, @i) = 0      
      BEGIN      
        INSERT @Tab      
        SELECT SUBSTRING(@String, @i,  9999)      
        BREAK      
      END        
      INSERT @Tab      
      SELECT SUBSTRING(@String, @i,  charindex(@char,@String,@i) - @i )    --, @i,  charindex(@char,@String,@i)      
      SET @i = charindex(@char,@String,@i)+1      
    END      
  END      
          
  RETURN       
END

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.75 (8)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.75 (8)

You rated this post out of 5. Change rating

Related content