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
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