Note - This is now built it to later versions of SQL Server Management Studio (SSMS)
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
sql
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.
sql
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 cut
A couple of notes
* You can’t amend the existing Alt+F1 entry so I went with Ctrl+F1 for my replacement.
* You need to open a new query window for the change to be picked up
So I can now highlight a table name and press Ctrl+F1
The completed script is sp_help_table_schemas.sql
Thanks go to Martin Bell who reviewed my stored procedure and give some valuable advice.
(Ported from https://sqlblogcasts.com/blogs/david-betteridge/archive/2012/03/20/sp-help.aspx)