{"id":875,"date":"2010-04-29T00:00:00","date_gmt":"2010-04-29T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/exploring-sql-server-table-metadata-with-ssms-and-tsql\/"},"modified":"2021-09-29T16:21:59","modified_gmt":"2021-09-29T16:21:59","slug":"exploring-sql-server-table-metadata-with-ssms-and-tsql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/exploring-sql-server-table-metadata-with-ssms-and-tsql\/","title":{"rendered":"Exploring SQL Server table metadata with SSMS and TSQL"},"content":{"rendered":"
\n

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. <\/p>\n

A dream? I’ll show you in this article how to make this happen, and lots more.<\/p>\n

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. <\/b>This appears in the results pane.<\/p>\n

\"1021-IHitControl6.jpg\"<\/p>\n

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.<\/p>\n

\"1021-IHitControl3.jpg\"<\/p>\n

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<\/p>\n

\"1021-IHitControl4.jpg\"<\/p>\n

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. <\/p>\n

\"1021-options.jpg\"<\/p>\n

 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!  <\/p>\n

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<\/p>\n

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. <\/p>\n

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)<\/p>\n

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.  <\/p>\n

There is a lot that can be done here like:….<\/p>\n

--list all the tables         \nSELECT name AS [Tables] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'isUserTable')<>0\n--list all the Scalar functions\nSELECT name AS [Scalar functions] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsScalarFunction')<>0\n--list all the Table Functions\nSELECT name AS [Table Functions] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsTableFunction')<>0\n--list all the Procedures\nSELECT name AS [Procedures] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsProcedure')<>0\n--list all the Triggers\nSELECT name AS [Triggers] FROM sys.objects WHERE OBJECTPROPERTY(object_id, 'IsTrigger')<>0\n<\/pre>\n
…And a host of other queries you can work out from here<\/a>. 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)<\/p>\n
SELECT\n  so.name+REPLACE(\n     '('+COALESCE((SELECT name+', ' FROM sys.parameters sp\n        WHERE sp.object_ID=so.object_ID AND parameter_ID>0\n      ORDER BY  parameter_ID\n      FOR XML PATH('')), '')+')',\n  ', )', ')')+COALESCE('  \/*'+CONVERT(VARCHAR(300), value)+'*\/','') [Scalar functions]\nFROM\n  sys.objects so\nLEFT OUTER JOIN sys.extended_properties ep \/*get any extended properties*\/\n  ON ep.name LIKE 'MS_Description' AND major_ID=so.object_ID\nWHERE\n  OBJECTPROPERTY(object_id, 'IsScalarFunction')<>0\n<\/pre>\n
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.<\/p>\n
SELECT\n  so.name+REPLACE(\n     ' ('+COALESCE((SELECT name+', ' FROM sys.columns sp\n        WHERE sp.object_ID=so.object_ID AND column_ID>0\n      ORDER BY  column_ID\n      FOR XML PATH('')), '')+')',\n  ', )', ')')+COALESCE('  \/*'+CONVERT(VARCHAR(300), value)+'*\/','') [Tables]\nFROM\n  sys.objects so\nLEFT OUTER JOIN sys.extended_properties ep \/* get any extended properties *\/\n  ON ep.name LIKE 'MS_Description' AND major_ID=so.object_ID and minor_ID=0\nWHERE\n  OBJECTPROPERTY(object_id, 'IsUserTable')<>0\n<\/pre>\n
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.  <\/p>\n
SELECT '\/* '+qualifiedName+' *\/'+CHAR(13)+CHAR(10)+REPLACE(\n         REPLACE( \n             STUFF(SelectScript, \/*delete final comma line-terminator*\/\n                              LEN(SelectScript)-CHARINDEX('|,|',\n                              REVERSE(SelectScript)+'|')-1,3\n                              ,'')\n                      ,'\\n',CHAR(13)+CHAR(10))\n         ,'|,|',',') \/*put in new-lines and convert token to comma*\/                       \nFROM (SELECT \n              so.name AS Name, \n              OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,\n         'SELECT '+REPLACE(COALESCE(\n                (\n                SELECT '\\n      '+QUOTENAME(sp.name)+'|,|'+COALESCE(' \/*'\n                       +CONVERT(VARCHAR(MAX),value)+'*\/','')\n                FROM sys.columns sp\n                            LEFT OUTER JOIN sys.extended_properties ep\n                           ON sp.object_id = ep.major_ID  \n                                   AND sp.column_ID = minor_ID AND class=1\n \n                WHERE sp.object_ID=so.object_ID\n                     AND column_ID>0\n                ORDER BY column_ID\n                FOR XML PATH('')\n               ),'1')\n          ,',||', '')\n          +'\\nFROM  '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))+'.'\n          +QUOTENAME(so.name)+COALESCE('  \/*'\n          +CONVERT(VARCHAR(300), value)+'*\/', '') [SelectScript]\n                                       \n       FROM\n         sys.objects so\n         LEFT OUTER JOIN sys.extended_properties ep\n              \/* get any extended properties *\/\n              ON ep.name LIKE 'MS_Description'\n                 AND major_ID=so.object_ID\n                 AND minor_ID=0\n       WHERE\n         OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f\nORDER BY name\n<\/pre>\n
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)<\/p>\n
\/* Person.Address *\/\nSELECT \n      [AddressID], \/*Primary key for Address records.*\/\n      [AddressLine1], \/*First street address line.*\/\n      [AddressLine2], \/*Second street address line.*\/\n      [City], \/*Name of the city.*\/\n      [StateProvinceID], \/*Unique identification number for the state or province. Foreign key to StateProvince table.*\/\n      [PostalCode], \/*Postal code for the street address.*\/\n      [rowguid], \/*ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.*\/\n      [ModifiedDate] \/*Date and time the record was last updated.*\/\nFROM  [Person].[Address]  \/*Street address information for customers, employees, and vendors.*\/\n<\/pre>\n
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.<\/p>\n
\"1021-tableScripts.jpg\"<\/p>\n
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.<\/p>\n
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. <\/p>\n
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<\/p>\n
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<\/b> 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<\/b> and sp_helptext.<\/b><\/p>\n
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,<\/a> 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.<\/p>\n
Why bother to look at table build scripts?<\/b><\/p>\n
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<\/b>) 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<\/b>‘ 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..<\/p>\n
 Under the hood.<\/b><\/p>\n
 With stored procedures, views, triggers and functions, SQL Server stores the source. This is easy to fetch out. <\/p>\n
--find the actual code for a particular stored procedure, view, function etc. \nSelect object_Name(object_ID),definition from sys.SQL_Modules\nwhere object_Name(object_ID)='vEmployeeDepartment'\n \n--find the actual code for a particular stored procedure, view, function etc. \nSelect name, object_definition(object_ID)\nfrom sys.objects\nwhere object_ID=object_ID('HumanResources.vEmployeeDepartment')\n<\/pre>\n
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. <\/p>\n
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. Oh no, this is for looking at.<\/p>\n
USE MASTER\nSET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\n \nGO\nIF EXISTS (SELECT 1 FROM sys.objects WHERE name LIKE 'sp_ScriptFor')\n       DROP PROCEDURE sp_ScriptFor\ngo\nCREATE PROCEDURE [dbo].[sp_ScriptFor]\n@Identifier NVARCHAR(776)\n\/**\n\tsummary:   >\n\t This procedure returns an object build script as a single-row, single column\n\t result.\n\t Unlike the built-in OBJECT_DEFINITION, it also does tables.\n\t It copies the SMO style where possible but it uses the more intuitive\n\t eay of representing referential constraints and includes the documentation\n\t as comments that was, for unknown reasons, left out by Microsoft.\n\t You call it with the name of the table, either as a string, a valid table name,\n\t or as a schema-qualified table name in a string.\n\tRevisions: \n\t - Author: Phil Factor\n\t   Version: 1.1\n\t   Modification: dealt properly with heaps\n\t   date: 20 Apr 2010\n\t - version: 1.2 \n\t   modification: Removed several bugs and got column-level constraints working\n\t   date: 1 Dec 2012\n\t - Version: 1.3 \n\t   Modification: Added extended properties build\n\t   date: 3 Dec 2012\n\texample:\n\t     - code: sp_ScriptFor 'production.product'\n\t     - code: sp_ScriptFor 'HumanResources.vEmployee'\n\t     - code: sp_Scriptfor 'person.person'\n\t     - code: execute AdventureWorks..sp_ScriptFor TransactionHistory\n\t     - code: sp_ScriptFor 'HumanResources.uspUpdateEmployeeHireInfo'\n\treturns:   >\n\t single row, single column result Build_Script.           \n \n**\/\n \nAS\nDECLARE @Script VARCHAR(MAX)\nDECLARE @dbname SYSNAME\nDECLARE @PrimaryKeyBuild VARCHAR(MAX)\nIF CHARINDEX ('.',@identifier)=0 --Add schema if none given\n       SELECT top 1 @Identifier=QUOTENAME(Object_Schema_name(s.object_id))\n                 +'.'+QUOTENAME(s.name)\n       FROM sys.objects s WHERE s.name LIKE @identifier\n \nSELECT @dbname = PARSENAME(@identifier,3)\n       IF @dbname IS NULL\n              SELECT @dbname = DB_NAME()\n       ELSE IF @dbname <> DB_NAME()\n              BEGIN\n                     RAISERROR(15250,-1,-1)\n                     RETURN(1)\n              END\n \nSELECT @Script=object_definition(OBJECT_ID(@Identifier))\nIF @script IS NULL\n       IF (SELECT TYPE FROM sys.objects\n           WHERE object_id=OBJECT_ID(@Identifier))\n      IN ('U','S')--if it is a table\n              BEGIN\n              SELECT @Script='\/*'+CONVERT(VARCHAR(2000),value)+'*\/\n'       FROM  sys.extended_properties ep\n                     WHERE ep.major_ID = OBJECT_ID(@identifier)\n                     AND  minor_ID=0 AND class=1\n \nSELECT @Script=COALESCE(@Script,'')+'CREATE TABLE '+@Identifier+'(\n   ' +\n(SELECT QUOTENAME(c.name)+ ' '+   coalesce(UDTs.UDTName,t.name)+' '\n       + CASE WHEN is_computed=1 THEN ' AS '+ --do DDL for a computed column\n                     (SELECT definition FROM sys.computed_columns cc\n                      WHERE cc.object_id=c.object_id AND cc.column_ID=c.column_ID)\n             + CASE WHEN\n                     (SELECT is_persisted \n                        FROM sys.computed_columns cc\n                        WHERE cc.object_id=c.object_id\n                        AND cc.column_ID=c.column_ID)\n                     =1 THEN 'PERSISTED' ELSE '' END\n              --we may have to put in the length         \n              WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '('+\n                 CASE WHEN c.max_length=-1 THEN 'MAX'\n                      ELSE CONVERT(VARCHAR(4),\n                                   CASE WHEN t.name IN ('nchar','nvarchar')\n                                   THEN  c.max_length\/2 ELSE c.max_length END )\n                      END +')'\n              WHEN t.name IN ('decimal','numeric')\n                      THEN '('+ CONVERT(VARCHAR(4),c.precision)+','\n                              + CONVERT(VARCHAR(4),c.Scale)+')'\n                      ELSE '' END\n              + CASE WHEN is_identity=1  THEN 'IDENTITY ('\n                     + CONVERT(VARCHAR(8),IDENT_SEED(Object_Schema_Name(c.object_id)\n                     +'.'+OBJECT_NAME(c.object_id)))+','\n                     + CONVERT(VARCHAR(8),IDENT_INCR(Object_Schema_Name(c.object_id)\n                     +'.'+OBJECT_NAME(c.object_id)))+')' ELSE '' END\n              + CASE WHEN c.is_rowguidcol=1 THEN ' ROWGUIDCOL' ELSE '' END\n              + CASE WHEN XML_collection_ID<>0 THEN --deal with object schema names\n                                  '('+ CASE WHEN is_XML_Document=1\n                                              THEN 'DOCUMENT ' ELSE 'CONTENT ' END\n                     + COALESCE(\n                         (SELECT QUOTENAME(ss.name)+'.' +QUOTENAME(sc.name)\n                          FROM sys.xml_schema_collections sc\n                          INNER JOIN  Sys.Schemas ss\n                              ON sc.schema_ID=ss.schema_ID\n                          WHERE sc.xml_collection_ID=c.XML_collection_ID)\n                       ,'NULL')\n                     +')' ELSE '' END\n              + CASE WHEN  is_identity=1\n                  THEN CASE WHEN OBJECTPROPERTY(object_id, 'IsUserTable') = 1\n                            AND COLUMNPROPERTY(object_id, c.name, 'IsIDNotForRepl') = 0\n                   AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0\n                THEN '' ELSE ' NOT FOR REPLICATION ' END ELSE '' END\n              + CASE WHEN c.is_nullable=0 THEN ' NOT NULL' ELSE ' NULL' END\n              + CASE WHEN c.default_object_id <>0\n                 THEN ' DEFAULT '+object_Definition(c.default_object_id) ELSE '' END\n              + CASE WHEN c.collation_name IS NULL THEN ''\n                 WHEN  c.collation_name<>\n                          (SELECT collation_name FROM sys.databases\n                             WHERE name=DB_NAME()) COLLATE Latin1_General_CI_AS\n                 THEN COALESCE(' COLLATE '+c.collation_name,'') ELSE '' END\n              +coalesce(TheCheck,'')+  coalesce(reference,'') +'|,|'  \n              + CASE WHEN ep.value IS NOT NULL\n                 THEN ' \/*'+CAST(value AS VARCHAR(100))+ '*\/' ELSE '' END\n              + CHAR(10)+'   '\n              FROM sys.columns c INNER JOIN sys.types t\n                     ON c.user_Type_ID=t.user_Type_ID\n              LEFT OUTER JOIN sys.extended_properties ep --join to the comments\n                    ON c.object_id = ep.major_ID \n                         AND c.column_ID = minor_ID AND class=1 \n                    left Outer join \n                      (Select column_ID,quotename(sys.schemas.name)+'.'+QuoteName(sys.types.name) as UDTname \n                   from sys.columns inner join sys.types\n                   on sys.types.user_type_id = sys.columns.user_type_id\n                   inner join sys.schemas\n                   on sys.schemas.schema_id=sys.types.schema_id\n                   where object_ID=object_ID(@identifier) \n                   and sys.columns.user_type_id <> sys.columns.system_type_id\n                  ) UDTs\n                on UDTs.column_ID=c.column_ID \n              inner join (SELECT cr.column_ID,(select case when is_system_named=0 then ' CONSTRAINT '+name else '' end+' CHECK '+definition \n                                  FROM sys.check_constraints ccr\n                                    where cr.column_ID = ccr.parent_column_id\n                                    and ccr.parent_object_ID = cr.Object_ID\n                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)') Thecheck\n                              FROM sys.columns cr \n                              where cr.Object_ID= OBJECT_ID(@identifier)) TheChecks\n           on theChecks.column_ID=c.column_ID\n              LEFT OUTER JOIN\n              (SELECT ' REFERENCES '\n           +COALESCE(SCHEMA_NAME(foreignRef.schema_ID)+'.','')\n           +OBJECT_NAME(fkc.referenced_object_id)+'('+c.name+') '--+\n              + CASE WHEN delete_referential_action_desc <> 'NO_ACTION'\n                                THEN 'ON DELETE '\n                                   + REPLACE(delete_referential_action_desc,'_',' ')\n                                                            COLLATE database_default\n                                ELSE '' END\n                       + CASE WHEN update_referential_action_desc <> 'NO_ACTION'\n                                THEN 'ON UPDATE '\n                                   + REPLACE(update_referential_action_desc,'_',' ')\n                                                            COLLATE database_default\n                                ELSE '' END\n                       AS reference, parent_column_id\n                     FROM sys.foreign_key_columns fkc \n                     INNER JOIN sys.foreign_keys fk ON constraint_object_id=fk.object_ID\n                     INNER JOIN sys.objects ForeignRef ON fkc.referenced_object_id=ForeignRef.Object_ID\n                     INNER JOIN sys.columns c\n                     ON c.object_ID = fkc.referenced_object_id\n                         AND c.column_ID = referenced_column_id\n                      WHERE fk.parent_object_ID = OBJECT_ID(@identifier)\n                     AND constraint_object_ID NOT IN --include only single-column keys\n                    (SELECT 1 FROM sys.foreign_key_columns multicolumn\n                               WHERE multicolumn.parent_object_id =fk.parent_object_ID\n                               GROUP BY constraint_object_id\n                               HAVING COUNT(*)>1)) column_references\n           ON  column_references.parent_column_ID=c.column_ID\n        WHERE object_id = OBJECT_ID(@identifier)\n        ORDER BY c.column_ID\n              FOR XML PATH(''), TYPE).value('.', 'varchar(max)')--join up all the rows!\n             \n              SELECT @Script=LEFT(@Script,LEN(@Script)-1)\n                           --take out the trailing line feed\n             \n              SELECT TOP 1 @PrimaryKeyBuild=  '\nCONSTRAINT ['+i.name+'] PRIMARY KEY '\n                     +CASE WHEN type_desc='CLUSTERED' THEN 'CLUSTERED' ELSE '' END+'\n   (\n          '   + COALESCE(SUBSTRING((SELECT ','+COL_NAME(ic.object_id,ic.column_id)\n              FROM  sys.index_columns AS ic\n              WHERE ic.index_ID=i.index_ID AND ic.object_id=i.object_id\n              ORDER BY key_ordinal\n              FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),2,2000),'?')+'\n   )WITH (PAD_INDEX  = '\n        +CASE WHEN is_Padded<>0 THEN 'ON' ELSE 'OFF' END\n        +',  IGNORE_DUP_KEY = '\n            +CASE WHEN ignore_dup_key<>0 THEN 'ON' ELSE 'OFF' END\n        +', ALLOW_ROW_LOCKS  = '\n            +CASE WHEN allow_row_locks<>0 THEN 'ON' ELSE 'OFF' END\n        +', ALLOW_PAGE_LOCKS  = '\n            +CASE WHEN allow_page_locks<>0 THEN 'ON' ELSE 'OFF' END\n        +') ON [PRIMARY]'+\n              + CASE WHEN ep.value IS NOT NULL THEN '\n  \/*'+CAST(value AS VARCHAR(100))+'*\/' ELSE '' END\n              FROM sys.indexes i\n              LEFT OUTER JOIN sys.extended_properties ep\n                     ON i.object_id = ep.major_ID  AND i.index_ID = minor_ID AND class=7\n              WHERE OBJECT_NAME(object_id)=PARSENAME(@identifier,1) \n                   AND is_primary_key =1\n              --and add the primary key build script and the ON PRIMARY, deleting the\n              --  last comma-line-terminator if necessary. conver the |,| to commas\n              --    \n              IF @PrimaryKeyBuild IS NULL\n                     SELECT @Script=STUFF(@Script,--delete final comma line-terminator\n                                  LEN(@Script)-CHARINDEX('|,|',\n                                  REVERSE(@Script)+'|')-1,3\n                                  ,'')\n\nDeclare @ExtendedPropertiesBuild Varchar(MAX)\nDeclare @ExtendedPropertyExecute Varchar(8000)\n\nSelect @ExtendedPropertyExecute='\nEXEC sys.sp_addextendedproperty N''MS_Description'', N''<value>'',\n      N''SCHEMA'', N'''+object_Schema_Name(OBJECT_ID(@Identifier))+''', N''TABLE'', N'''+object_Name(OBJECT_ID(@Identifier))+'''' \n\nSelect @ExtendedPropertiesBuild=\n(Select Replace(@ExtendedPropertyExecute,'<value>',Replace(convert(varchar(max),value),'''',''''''))\n            +Coalesce(', '''+Level2Type+''', '''+Level2Name+'''','')\nfrom (      \n      select value, null as level2type ,null as level2Name\n        from sys.extended_properties ep\n                                     WHERE ep.major_ID = OBJECT_ID(@Identifier)\n                                     AND  minor_ID=0 AND class=1\n      union all \n       \n      select Value,'COLUMN',sys.columns.name\n        from sys.extended_properties ep\n        inner join sys.columns\n        on sys.columns.column_ID=minor_ID\n        and sys.columns.object_ID=major_ID\n        WHERE ep.major_ID = OBJECT_ID(@Identifier)\n                                     AND  class=1 AND  minor_ID>0\n      union all\n      Select Value,'CONSTRAINT',object_name(ep.major_ID) \n      from sys.extended_properties ep\n      inner join sys.objects on object_ID=major_ID\n      where parent_Object_ID=OBJECT_ID(@Identifier)\n      and minor_ID=0\n      union all\n      Select Value,'INDEX', sys.indexes.name \n      from sys.extended_properties ep\n      inner join sys.indexes on object_ID=major_ID and index_ID=minor_ID\n      where major_ID=OBJECT_ID(@Identifier)\n      and class=7 and minor_ID>0\n      )f\n      FOR XML PATH(''), TYPE).value('.', 'varchar(max)')  \n                                  \n              SELECT @Script=REPLACE(@Script,'|,|',',')+COALESCE(@PrimaryKeyBuild,'')+'\n) ON [PRIMARY]'+ Coalesce('\n\n\/* and the extended properties associated with the table and its indexes *\/'+ @ExtendedPropertiesBuild,'')     \n\nEND\nSELECT COALESCE(@Script,'-- could not find '''+@identifier+''' in '+DB_NAME(),'null identifier.')\n    AS Build_Script\nGO\nIF NOT EXISTS\n  (SELECT 1 FROM sys.objects WHERE NAME = 'sp_ScriptFor' AND IS_MS_SHIPPED=1)\n   EXEC sp_ms_marksystemobject 'sp_ScriptFor'\nGO\n \nGO \n<\/pre>\n
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.<\/p>\n
See also the first two articles in this series
      Finding Stuff in SQL Server Database DDL<\/a>
      Exploring your database schema with SQL<\/a><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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 his series on exploring your database schema with SQL.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4150,4151,4252,4190],"coauthors":[],"class_list":["post-875","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-sql-server","tag-t-sql-programming","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=875"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/875\/revisions"}],"predecessor-version":[{"id":92550,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/875\/revisions\/92550"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=875"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}