sp_help
- by David-Betteridge
One of the nice things about SQL Server Management Studio (SSMS) is that you
can highlight a table name in a script and press Alt + F1 to perform sp_help on
it.
Unfortunately I've never been able to use that feature as the majority of
the tables in our product belong to a schema other than dbo. On a long train journey back to York I wondered if I could
solve this problem by writing my own replacement for sp_help (which I’ve called sp_help_table_schemas).
My version works by first checking the system tables to find
out which schemas the table belongs to
SELECT s.Name --Find the schema FROM sys.schemas s
JOIN sys.tables t on t.schema_id = s.schema_id
WHERE t.name = 'Orders'It then dynamically calls the standard sp_help method but this time supplying the table owner as well.SET @cmd = 'EXEC sp_help ''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) + ''' ;' ;
EXEC ( @cmd )Once I had proved the basics worked I wrapped it up into a
stored procedure and deployed it to the master database on my laptop. It was then just a question of going into
Tools à
Options within SSMS and defining the keyboard short cutA couple of notes
You can’t amend the existing Alt+F1 entry to I
went with Ctrl+F1.
You need to open new query window for the change
to be picked upSo I can now highlight a table name and press Ctrl+F1 The completed script is attached. Thanks go to Martin Bell who reviewed my
stored procedure and give some valuable advice.
