sp_help
Posted
by David-Betteridge
on SQL Blogcasts
See other posts from SQL Blogcasts
or by David-Betteridge
Published on Tue, 20 Mar 2012 18:14:00 GMT
Indexed on
2012/03/20
23:35 UTC
Read the original article
Hit count: 332
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 cut
A 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 up
So 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.
© SQL Blogcasts or respective owner
