Two useful functions that you should bind in SSMS
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
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.