Two useful functions that you should bind in SSMS

No Comments

So this is my first post in a while so I though we could look at two useful SSMS customizations that I use somewhere around 20-150 times each day. (Yes, those figures are directly correlated to the length of my workday.)

Both of them include binding functions to CTRL + F1 and CTRL + 3 (do you want them on other shortcuts feel free to chose your own). If you don’t know how to bind, just start SSMS (SQL Server Management Studio) and navigate to  -> Tools -> Options -> Keyboard -> Query Shortcuts. There you can see what you already have there, i.e. the default functions and the room you have for new functionality. It should look something like this:

Now to the commands.

1) Okay so let’s start with the first one that I have on CTRL + F1:

  • SELECT TOP 1000 * FROM 
    (Remember the trailing space.) What this will do is that when you mark a table and click CTRL + F1 it will automatically select the top 1000 rows from it without you have to do anything more. In my opinion really convenient when you want to glance into a table that you don’t know the structure or the content of.

2) The second one is, to be honest with you, a bit redundant. Because you might as well use sp_help (bound to ALT + F1) but it gives you almost too much information. Therefore I created this one:

        -- =====================================================================
-- Author:      mattssok
-- Create date: YYYY-MM-DD
-- Description: This procedure returns the table definition for the 
--              current object
--              The object must reside in the same database as the current --              context to return desired result.
-- =====================================================================

ALTER   PROCEDURE [dbo].[usp_GetTableDefinition] 
  @FullTableName NVARCHAR(75) = NULL

AS

-- Error handling reference: http://www.sommarskog.se/error_handling/Part1.html
SET XACT_ABORT, NOCOUNT ON 

BEGIN TRY

  DECLARE @DbName NVARCHAR(50);
  SELECT @DbName = DB_NAME(); 

  -- Save and execute current database statement to make sure we operate - 
  -- in the right context
  DECLARE @SqlQry NVARCHAR(100) = N'USE ' + '[' + @DbName + ']';
  EXEC sp_executesql  @SqlQry

  -- Error handling block from sp_help to ensure us that the object in question is in the right database
  -- Start of block
  DECLARE @ObjId int, @SchemaId int, @Schema as NVARCHAR(3)
  --SELECT @ObjId = object_id FROM sys.all_objects WHERE object_id = object_id(@FullTableName)
  SELECT @ObjId = object_id, @SchemaId = schema_id FROM sys.all_objects WHERE name = @FullTableName
  SELECT @Schema = name FROM sys.schemas WHERE schema_id = @SchemaId 

  --select * FROM sys.all_objects WHERE object_id = object_id(N'lho.[InvInvoice]', 'U')

  -- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
  IF @ObjId is null
  BEGIN

    SELECT @ObjId = type_id(@FullTableName)

    -- IF NOT IN SYSTYPES, GIVE UP
    IF @objid is null
    BEGIN
      RAISERROR(15009,-1, -1, @FullTableName, @DbName)
      RETURN(1)
    END
  END
  -- End of block

  BEGIN TRANSACTION
        
    -- Start creating the table definition
    -- Please note that I have borrowed quite a bit from https://www.c-sharpcorner.com/UploadFile/67b45a/how-to-generate-a-create-table-script-for-an-existing-table/ 
    -- so please have a look there if you want the full story or just a more knowledgeable resource
    SELECT 
      ColumnName = CHAR(9) + '[' + c.name + '] '
     ,ColumnType = 
       CASE 
         WHEN c.is_computed = 1 
         THEN 'AS ' + cc.[definition] 
         ELSE UPPER(tp.name) + 
           CASE 
             WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
             THEN '(' + 
               CASE 
                 WHEN c.max_length = -1 
                 THEN 'MAX' 
                 ELSE CAST(c.max_length AS VARCHAR(5)) 
               END + ')'
             WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')
             THEN '(' + 
               CASE 
                 WHEN c.max_length = -1 
                 THEN 'MAX' 
                 ELSE CAST(c.max_length / 2 AS VARCHAR(5)) 
               END + ')'
             WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset') 
             THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'
             WHEN tp.name = 'decimal' 
             THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
             ELSE ''
           END +
         CASE 
           WHEN c.is_nullable = 1 
           THEN ' NULL' 
           ELSE ' NOT NULL' 
         END +
         CASE 
           WHEN dc.[definition] IS NOT NULL 
           THEN ' DEFAULT' + dc.[definition] 
           ELSE '' 
         END + 
         CASE 
           WHEN ic.is_identity = 1 
           THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' 
           ELSE '' 
         END 
       END + CHAR(13)
    FROM 
      sys.columns c WITH (NOWAIT)
    INNER JOIN 
      sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id
    INNER JOIN 
      sys.objects o WITH (NOWAIT) ON o.object_id = c.object_id
    INNER JOIN 
      sys.schemas s WITH (NOWAIT) ON o.schema_id = s.schema_id
    LEFT JOIN 
      sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id
    LEFT JOIN 
      sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id
    LEFT JOIN 
      sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id
    WHERE
	  o.object_id = @ObjId
    ORDER BY 
      c.column_id

  COMMIT TRANSACTION -- End of transaction

END TRY -- End of try
 
BEGIN CATCH
   IF @@TRANCOUNT > 0 
  ROLLBACK TRANSACTION
  -- Error procedure and basic procedure template from http://www.sommarskog.se/error_handling/Part1.html
  EXEC ctr.usp_error_handler    
  RETURN 55555
END CATCH

Output

What this will do is that when you highlight a table and, in my case, press CTRL + 3 it will give you all the columns and their definition for the current table. For me this is really convenient if, e.g. I get an truncation error when running a merge so I can get a quick picture of the table and change the table accordingly.

That was all for this time. Please leave a comment if you had any use of this or just for some other reason.

//mattssok

Leave a Reply

Your email address will not be published. Required fields are marked *