Phil shows how to start squeezing powerful magic from SSMS for doing a detailed exploration of the metadata of your routines and tables, In this third part to the series on exploring your database schema with SQL.
When you are using Management Studio, it would be nice to be able to select the name of an object, particularly a table, and to instantly see, at a keystroke, the build script in the results pane, complete with the documentation and, in the case of a table, the referential constraints. It would also be nice to get a list of all the places in the database where the object’s name appears.
A dream? I’ll show you in this article how to make this happen, and lots more.
First, a brief demo of what I mean. I have a blank query window. I wonder what tables I have in my database and what they're for. I hit my query shortcut Control 6. This appears in the results pane.
We have a list of tables for the database we are investigating (OK! It is AdventureWorks, i'll admit). Hmm. We’ll pop them into the query pane in order to explore them. So, what is this table ‘ProductReview?. We hit Control 3 and instantly out it pops, the build script.
I have turned this query window into a powerful table-inspector, but I could have done it for procedures, functions or triggers. I can do other things, of course. How about telling me where the string 'productReview' occurs? Select it and hit cntl 4
How do I do this? There exists in SSMS a very useful facility for executing particular stored procedures or simple SQL expressions of your choice. There are a number of special keyboard shortcuts that are reserved for the user. These are called the Query Shortcuts and are accessed with ALT F1 Ctl F1, and Ctl 1 …Ctl 0.
Normally, you use these to invoke the system stored procedures sp_Help, sp_HelpText and sp_Who. The other nine slots are left enticingly blank for your own routines or queries. You can get in there and add your own, but these will only work in Query Windows that you open subsequently!
Of course, for information such as that build script you’ll probably need to set the results window to text, and increase the no. of characters displayed in each column to a sensible level such as 8000 chars
If you highlight text in the code pane of the query window in SSMS or QA, and then invoke code that is slotted into one of these keyboard shortcuts, whatever you highlight is appended ‘as-is’ to what is executed. This means that if you want to pass a string to a procedure it will need to be a valid delimited string, escaped if necessary. If it is a valid object name, it is coerced successfully into a string. This means that you can pass the names of an object, but a qualified object name would have to be delimited properly.
Most of the code that I have in Query Shortcuts in my copy of SSMS is for getting lists of tables, procedures, parameters and so on. I haven’t hit a limit for the length of the expression, but it has to be all in one line. (see the screen-scrape of the Options tab above)
There are a number of queries that can go in one line that provide useful information before you become forced to use a stored procedure. The sort of queries that work are the ones that don’t require parameters.
There is a lot that can be done here like:….
--list all the tables
SELECT name AS [Tables] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'isUserTable')<>0
--list all the Scalar functions
SELECT name AS [Scalar functions] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsScalarFunction')<>0
--list all the Table Functions
SELECT name AS [Table Functions] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsTableFunction')<>0
--list all the Procedures
SELECT name AS [Procedures] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsProcedure')<>0
--list all the Triggers
SELECT name AS [Triggers] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsTrigger')<>0
...And a host of other queries you can work out from here. Of course, you can elaborate them. Here is some code that shows you all your functions along with their parameters, and any extended property: (but I won’t show it to you all in one long line as it will have to be for use)
SELECT
so.name+REPLACE(
'('+COALESCE((SELECT name+', ' FROM sys.parameters sp
WHERE sp.object_ID=so.object_ID AND parameter_ID>0
ORDER BY parameter_ID
FOR XML PATH('')), '')+')',
', )', ')')+COALESCE(' /*'+CONVERT(VARCHAR(300), value)+'*/','') [Scalar functions]
FROM
sys.objects so
LEFT OUTER JOIN sys.extended_properties ep /*get any extended properties*/
ON ep.name LIKE 'MS_Description' AND major_ID=so.object_ID
WHERE
OBJECTPROPERTY(object_id, 'IsScalarFunction')<>0
With a moment’s thought, you’ll see a number of possibilities. In the past two articles in this series, I’ve given a few ideas: There are plenty more. What, for example, about listing out all tables along with a list of columns that can then be used for Select and update statements? Easy. Then you can just keep the list handy somewhere when doing some development work (I use AceText but you can use Notepad if you have nothing better). Here is the routine for the Table-lister I used earlier.
SELECT
so.name+REPLACE(
' ('+COALESCE((SELECT name+', ' FROM sys.columns sp
WHERE sp.object_ID=so.object_ID AND column_ID>0
ORDER BY column_ID
FOR XML PATH('')), '')+')',
', )', ')')+COALESCE(' /*'+CONVERT(VARCHAR(300), value)+'*/','') [Tables]
FROM
sys.objects so
LEFT OUTER JOIN sys.extended_properties ep /* get any extended properties */
ON ep.name LIKE 'MS_Description' AND major_ID=so.object_ID and minor_ID=0
WHERE
OBJECTPROPERTY(object_id, 'IsUserTable')<>0
With a moment's thought, you'll notice that you can elaborate this to give you the complete select statement for tables, including all the comments, for all your database tables. This is suddenly powerful magic, particularly as you can take out the new-lines and it all executes fine from a Query Shortcut key.
SELECT '/* '+qualifiedName+' */'+CHAR(13)+CHAR(10)+REPLACE(
REPLACE(
STUFF(SelectScript, /*delete final comma line-terminator*/
LEN(SelectScript)-CHARINDEX('|,|',
REVERSE(SelectScript)+'|')-1,3
,'')
,'\n',CHAR(13)+CHAR(10))
,'|,|',',') /*put in new-lines and convert token to comma*/
FROM (SELECT
so.name AS Name,
OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,
'SELECT '+REPLACE(COALESCE(
(
SELECT '\n '+QUOTENAME(sp.name)+'|,|'+COALESCE(' /*'
+CONVERT(VARCHAR(MAX),value)+'*/','')
FROM sys.columns sp
LEFT OUTER JOIN sys.extended_properties ep
ON sp.object_id = ep.major_ID
AND sp.column_ID = minor_ID AND class=1
WHERE sp.object_ID=so.object_ID
AND column_ID>0
ORDER BY column_ID
FOR XML PATH('')
),'1')
,',||', '')
+'\nFROM '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))+'.'
+QUOTENAME(so.name)+COALESCE(' /*'
+CONVERT(VARCHAR(300), value)+'*/', '') [SelectScript]
FROM
sys.objects so
LEFT OUTER JOIN sys.extended_properties ep
/* get any extended properties */
ON ep.name LIKE 'MS_Description'
AND major_ID=so.object_ID
AND minor_ID=0
WHERE
OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f
ORDER BY name
This routine will create a select statement for every table in your database, including both table and column comments in extended properties. This will end up looking like this (Just one table in my sample)
/* Person.Address */
SELECT
[AddressID], /*Primary key for Address records.*/
[AddressLine1], /*First street address line.*/
[AddressLine2], /*Second street address line.*/
[City], /*Name of the city.*/
[StateProvinceID], /*Unique identification number for the state or province. Foreign key to StateProvince table.*/
[PostalCode], /*Postal code for the street address.*/
[rowguid], /*ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.*/
[ModifiedDate] /*Date and time the record was last updated.*/
FROM [Person].[Address] /*Street address information for customers, employees, and vendors.*/
You may wonder why I put the name of the table in comments at the start. This is so that it is easier to locate the table build script if your results pane is set to 'grid' view.
The script for Table-Valued Functions is even more complex, but this and the script for executing procedures can be useful if you take care to document your code using extended properties. (I use SQL Doc to make this easier to do). Your code starts looking a lot more readable and understandable.
Every grey-muzzled database programmer will have a ‘thumb-drive’ of favourite utility queries and routines to ease the development process. If you are clever with these, the requirement to continually poke and click all over SSMS to get anything done soon diminishes to a tolerable level and so your subsequent development work can get pretty much faster.
You’ll notice a catch when you want to get to the next level of complexity. We want to highlight the name of a routine or table and hit a keyboard shortcut to get a build script and see what is really going on. We can’t use a query since the parameter ends up being appended. This is killer. We have to use a stored procedure
Here we hit a particular problem, in that these keyboard query shortcuts are designed purely for use by system stored procedures, and they don’t easily lend themselves to use with normal stored procedures unless you propagate them to every database you are working on. As a general practice, I put my kit of development tools in Model in a dev schema so it automatically gets propagated to all my development databases as I create them. However, this is useless for a keyboard-shortcut tool and it can end up being accidentally included in a deployment. You’d have thought that a safer alternative would be to create a special ‘Dev’ database for all your metadata-pummelling tools, but this would mean that your tools could only be evoked for that database! The only alternative to placing your dev routines in each database is to put them in the MASTER database. We are faced with needing to make a special plea to the DBA to be allowed to do this, add the sp_ prefix, and register the stored procedure as a system stored procedure, but you would end up having to redo it on every service pack and upgrade. Putting routines into the MASTER database isn’t generally a good idea, but I’m afraid that this particular extension of SSMS requires it if you wish to have more than the standard development stored procedures like sp_help and sp_helptext.
The magic of searching for where a string occurs in a database was done with the procedure I gave you at the end of the first article in this series. You'll find it here, but you'll have to change its name by giving an 'sp_' prefix and put it in the master database, makking sure you register it too.
Why bother to look at table build scripts?
If you haven’t got SQL Prompt, table build scripts can be gotten from SSMS just by opening up the browser, clicking on the database, clicking on ‘tables’, and then right-clicking on the table you need information for. Then you need to select ‘script tables as’ and finally choose a suitable target. When you finally get the table-build script, you’ll see that it isn’t designed for humans to see. The comments (MS_Description) for each column aren’t shown with the table, and the description of the table is lost half-way down the page. It isn’t usually clear which columns are foreign keys and what they are referring to. (they don’t use the clearer ‘REFERENCES’ syntax for single-column foreign key constraints) It isn’t programmer-friendly. You'll soon detect that doing things this way is fine for the database of your CD collection but not much else. For serious exploration of metadata, you need something much better. Ideally, of course, you'll have everything to hand using SQL Doc, or some other third-party documenter, but the method I'm describing isn't bad, and can be honed to your exact requirements..
Under the hood.
With stored procedures, views, triggers and functions, SQL Server stores the source. This is easy to fetch out.
--find the actual code for a particular stored procedure, view, function etc.
Select object_Name(object_ID),definition from sys.SQL_Modules
where object_Name(object_ID)='vEmployeeDepartment'
--find the actual code for a particular stored procedure, view, function etc.
Select name, object_definition(object_ID)
from sys.objects
where object_ID=object_ID('HumanResources.vEmployeeDepartment')
If fetching table scripts were that easy, you wouldn't need the rather scary script at the end of this article. However, tables aren't held in script form in SQL Server because it would be difficult to synchronise the script with any changes you made with its child objects such as columns or constraints. SSMS uses SMO to reconstitute the build script. It is an elaborate process. Unike MySQL, there is no SQL command to produce a build script. Either we have to use SMO, or hand-craft a stored procedure to do it.
There is a good reason why table-build scripts do not proliferate in SQL Server Blogs. They are hard to get right, and they're a moving target with every revision of SQL Server. Here is my take on the problem, which aims to provide the script for any object. Remember, please, before you use this, that these are intended to allow you to get information about your objects such as tables, functions, procedures and so on. The table section, in particular will not give you a complete build script as I don't bother with indexes and check constraints, or all those messy extended property build expressions. Oh no, this is for looking at.
USE MASTER
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE name LIKE 'sp_ScriptFor')
DROP PROCEDURE sp_ScriptFor
go
CREATE PROCEDURE [dbo].[sp_ScriptFor]
@Identifier NVARCHAR(776)
/**
summary: >
This procedure returns an object buld script as a single-row, single column
result.
Unlike the built-in OBJECT_DEFINITION, it also does tables.
It copies the SMO style where possible but it uses the more intuitive
eay of representing referential constrants and includes the documentation
as comments that was, for unknown reasons, left out by microsoft.
You call it with the name of the table, either as a string, a valid table name,
or as a schema-qualified table name in a string.
Author: Phil Factor
Revision: 1.1 dealt properly with heaps
date: 20 Apr 2010
example:
- code: sp_ScriptFor 'production.TransactionHistory'
example:
- code: sp_ScriptFor 'HumanResources.vEmployee'
example:
- code: execute phone..sp_ScriptFor 'holidays'
example:
- code: execute AdventureWorks..sp_ScriptFor TransactionHistory
example:
- code: sp_ScriptFor 'HumanResources.uspUpdateEmployeeHireInfo'
returns: >
single row, single column result Build_Script.
**/
--sp_helptext sp_help 'jobcandidate'
AS
DECLARE @Script VARCHAR(MAX)
DECLARE @dbname SYSNAME
DECLARE @PrimaryKeyBuild VARCHAR(MAX)
IF CHARINDEX ('.',@identifier)=0
SELECT @Identifier=QUOTENAME(Object_Schema_name(s.object_id))
+'.'+QUOTENAME(s.name)
FROM sys.objects s WHERE s.name LIKE @identifier
SELECT @dbname = PARSENAME(@identifier,3)
IF @dbname IS NULL
SELECT @dbname = DB_NAME()
ELSE IF @dbname <> DB_NAME()
BEGIN
RAISERROR(15250,-1,-1)
RETURN(1)
END
SELECT @Script=object_definition(OBJECT_ID(@Identifier))
IF @script IS NULL
IF (SELECT TYPE FROM sys.objects
WHERE object_id=OBJECT_ID(@Identifier))
IN ('U','S')--if it is a table
BEGIN
SELECT @Script='/*'+CONVERT(VARCHAR(2000),value)+'*/
' FROM sys.extended_properties ep
WHERE ep.major_ID = OBJECT_ID(@identifier)
AND minor_ID=0 AND class=1
SELECT @Script=COALESCE(@Script,'')+'CREATE TABLE '+@Identifier+'(
' +
(SELECT QUOTENAME(c.name)+ ' '+ t.name+' '
+ CASE WHEN is_computed=1 THEN ' AS '+ --do DDL for a computed column
(SELECT definition FROM sys.computed_columns cc
WHERE cc.object_id=c.object_id AND cc.column_ID=c.column_ID)
+ CASE WHEN
(SELECT is_persisted FROM sys.computed_columns cc
WHERE cc.object_id=c.object_id
AND cc.column_ID=c.column_ID)
=1 THEN 'PERSISTED' ELSE '' END
--we may have to put in the length
WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '('+
CASE WHEN c.max_length=-1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),
CASE WHEN t.name IN ('nchar','nvarchar')
THEN c.max_length/2 ELSE c.max_length END )
END +')'
WHEN t.name IN ('decimal','numeric')
THEN '('+ CONVERT(VARCHAR(4),c.precision)+','
+ CONVERT(VARCHAR(4),c.Scale)+')'
ELSE '' END
+ CASE WHEN is_identity=1 THEN 'IDENTITY ('
+ CONVERT(VARCHAR(8),IDENT_SEED(Object_Schema_Name(c.object_id)
+'.'+OBJECT_NAME(c.object_id)))+','
+ CONVERT(VARCHAR(8),IDENT_INCR(Object_Schema_Name(c.object_id)
+'.'+OBJECT_NAME(c.object_id)))+')' ELSE '' END
+ CASE WHEN c.is_rowguidcol=1 THEN ' ROWGUIDCOL' ELSE '' END
+ CASE WHEN XML_collection_ID<>0 THEN --deal with object schema names
'('+ CASE WHEN is_XML_Document=1
THEN 'DOCUMENT ' ELSE 'CONTENT ' END
+ COALESCE(
(SELECT QUOTENAME(ss.name)+'.' +QUOTENAME(sc.name)
FROM sys.xml_schema_collections sc
INNER JOIN Sys.Schemas ss
ON sc.schema_ID=ss.schema_ID
WHERE sc.xml_collection_ID=c.XML_collection_ID)
,'NULL')
+')' ELSE '' END
+ CASE WHEN is_identity=1
THEN CASE WHEN OBJECTPROPERTY(object_id, 'IsUserTable') = 1
AND COLUMNPROPERTY(object_id, c.name, 'IsIDNotForRepl') = 0
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
THEN '' ELSE ' NOT FOR REPLICATION ' END ELSE '' END
+ CASE WHEN c.is_nullable=0 THEN ' NOT NULL' ELSE ' NULL' END
+ CASE WHEN c.default_object_id <>0
THEN ' DEFAULT '+object_Definition(c.default_object_id) ELSE '' END
+ CASE WHEN c.collation_name IS NULL THEN ''
WHEN c.collation_name<>
(SELECT collation_name FROM sys.databases
WHERE name=DB_NAME()) COLLATE Latin1_General_CI_AS
THEN COALESCE(' COLLATE '+c.collation_name,'') ELSE '' END+'|,|'
+ CASE WHEN ep.value IS NOT NULL
THEN ' /*'+CAST(value AS VARCHAR(100))+ '*/' ELSE '' END
+ CHAR(10)+' '
FROM sys.columns c INNER JOIN sys.types t
ON c.user_Type_ID=t.user_Type_ID
LEFT OUTER JOIN sys.extended_properties ep
ON c.object_id = ep.major_ID
AND c.column_ID = minor_ID AND class=1
LEFT OUTER JOIN
(SELECT 'REFERENCES '
+COALESCE(SCHEMA_NAME(fkc.referenced_object_id)+'.','')
+OBJECT_NAME(fkc.referenced_object_id)+'('+c.name+') '--+
+ CASE WHEN delete_referential_action_desc <> 'NO_ACTION'
THEN 'ON DELETE '
+ REPLACE(delete_referential_action_desc,'_',' ')
COLLATE database_default
ELSE '' END
+ CASE WHEN update_referential_action_desc <> 'NO_ACTION'
THEN 'ON UPDATE '
+ REPLACE(update_referential_action_desc,'_',' ')
COLLATE database_default
ELSE '' END
AS reference, parent_column_id
FROM sys.foreign_key_columns fkc
INNER JOIN sys.foreign_keys fk ON constraint_object_id=fk.object_ID
INNER JOIN sys.columns c
ON c.object_ID = fkc.referenced_object_id
AND c.column_ID = referenced_column_id
WHERE fk.parent_object_ID = OBJECT_ID(@identifier)
AND constraint_object_ID NOT IN --include only single-column keys
(SELECT 1 FROM sys.foreign_key_columns multicolumn
WHERE multicolumn.parent_object_id =fk.parent_object_ID
GROUP BY constraint_object_id
HAVING COUNT(*)>1)) column_references
ON column_references.parent_column_ID=c.column_ID
WHERE object_id = OBJECT_ID(@identifier)
ORDER BY c.column_ID
FOR XML PATH(''))--join up all the rows!
SELECT @Script=LEFT(@Script,LEN(@Script)-1)
--take out the trailing line feed
SELECT TOP 1 @PrimaryKeyBuild= '
CONSTRAINT ['+i.name+'] PRIMARY KEY '
+CASE WHEN type_desc='CLUSTERED' THEN 'CLUSTERED' ELSE '' END+'
(
' + COALESCE(SUBSTRING((SELECT ','+COL_NAME(ic.object_id,ic.column_id)
FROM sys.index_columns AS ic
WHERE ic.index_ID=i.index_ID AND ic.object_id=i.object_id
ORDER BY key_ordinal
FOR XML PATH('')),2,2000),'?')+'
)WITH (PAD_INDEX = '
+CASE WHEN is_Padded<>0 THEN 'ON' ELSE 'OFF' END
+', IGNORE_DUP_KEY = '
+CASE WHEN ignore_dup_key<>0 THEN 'ON' ELSE 'OFF' END
+', ALLOW_ROW_LOCKS = '
+CASE WHEN allow_row_locks<>0 THEN 'ON' ELSE 'OFF' END
+', ALLOW_PAGE_LOCKS = '
+CASE WHEN allow_page_locks<>0 THEN 'ON' ELSE 'OFF' END
+') ON [PRIMARY]'+
+ CASE WHEN ep.value IS NOT NULL THEN '
/*'+CAST(value AS VARCHAR(100))+'*/' ELSE '' END
FROM sys.indexes i
LEFT OUTER JOIN sys.extended_properties ep
ON i.object_id = ep.major_ID AND i.index_ID = minor_ID AND class=7
WHERE OBJECT_NAME(object_id)=PARSENAME(@identifier,1)
AND is_primary_key =1
--and add the primary key build script and the ON PRIMARY, deleting the
-- last comma-line-terminator if necessary. conver the |,| to commas
--
IF @PrimaryKeyBuild IS NULL
SELECT @Script=STUFF(@Script,--delete final comma line-terminator
LEN(@Script)-CHARINDEX('|,|',
REVERSE(@Script)+'|')-1,3
,'')
SELECT @Script=REPLACE(@Script,'|,|',',')+COALESCE(@PrimaryKeyBuild,'')+'
) ON [PRIMARY]'
END
SELECT COALESCE(@Script,'-- could not find '''+@identifier+''' in '+DB_NAME(),'null identifier.')
AS Build_Script
GO
IF NOT EXISTS
(SELECT 1 FROM sys.objects WHERE NAME = 'sp_ScriptFor' AND IS_MS_SHIPPED=1)
EXEC sp_ms_marksystemobject 'sp_ScriptFor'
GO
So all you need to do now is to collect up the other scripts you find useful and configure up your SSMS Query Shortcuts to give you extra speed for your database development work, especially if you are refactoring someone else's database. The reason I like doing this sort of thing is because I like to hone my development environment to my own particular tastes. Your tastes will be different, but I hope you agree with the principle that it is good to take some time to make sure you can develop things quickly and without frustrating delays. There is nothing more frustrating than wrestling with an IDE designed by people who don't seem to understand how database developers do their work.
See also the first two articles in this series
Finding Stuff in SQL Server Database DDL
Exploring your database schema with SQL
