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:….
|
1 2 3 4 5 6 7 8 9 10 |
--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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 |
/* 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.
|
1 2 3 4 5 6 7 8 |
--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. Oh no, this is for looking at.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 |
USE MASTER SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO 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 build 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 constraints 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. Revisions: - Author: Phil Factor Version: 1.1 Modification: dealt properly with heaps date: 20 Apr 2010 - version: 1.2 modification: Removed several bugs and got column-level constraints working date: 1 Dec 2012 - Version: 1.3 Modification: Added extended properties build date: 3 Dec 2012 example: - code: sp_ScriptFor 'production.product' - code: sp_ScriptFor 'HumanResources.vEmployee' - code: sp_Scriptfor 'person.person' - code: execute AdventureWorks..sp_ScriptFor TransactionHistory - code: sp_ScriptFor 'HumanResources.uspUpdateEmployeeHireInfo' returns: > single row, single column result Build_Script. **/ AS DECLARE @Script VARCHAR(MAX) DECLARE @dbname SYSNAME DECLARE @PrimaryKeyBuild VARCHAR(MAX) IF CHARINDEX ('.',@identifier)=0 --Add schema if none given SELECT top 1 @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)+ ' '+ coalesce(UDTs.UDTName,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 +coalesce(TheCheck,'')+ coalesce(reference,'') +'|,|' + 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 --join to the comments ON c.object_id = ep.major_ID AND c.column_ID = minor_ID AND class=1 left Outer join (Select column_ID,quotename(sys.schemas.name)+'.'+QuoteName(sys.types.name) as UDTname from sys.columns inner join sys.types on sys.types.user_type_id = sys.columns.user_type_id inner join sys.schemas on sys.schemas.schema_id=sys.types.schema_id where object_ID=object_ID(@identifier) and sys.columns.user_type_id <> sys.columns.system_type_id ) UDTs on UDTs.column_ID=c.column_ID inner join (SELECT cr.column_ID,(select case when is_system_named=0 then ' CONSTRAINT '+name else '' end+' CHECK '+definition FROM sys.check_constraints ccr where cr.column_ID = ccr.parent_column_id and ccr.parent_object_ID = cr.Object_ID FOR XML PATH(''), TYPE).value('.', 'varchar(max)') Thecheck FROM sys.columns cr where cr.Object_ID= OBJECT_ID(@identifier)) TheChecks on theChecks.column_ID=c.column_ID LEFT OUTER JOIN (SELECT ' REFERENCES ' +COALESCE(SCHEMA_NAME(foreignRef.schema_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.objects ForeignRef ON fkc.referenced_object_id=ForeignRef.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(''), TYPE).value('.', 'varchar(max)')--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(''), TYPE).value('.', 'varchar(max)'),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 ,'') Declare @ExtendedPropertiesBuild Varchar(MAX) Declare @ExtendedPropertyExecute Varchar(8000) Select @ExtendedPropertyExecute=' EXEC sys.sp_addextendedproperty N''MS_Description'', N''<value>'', N''SCHEMA'', N'''+object_Schema_Name(OBJECT_ID(@Identifier))+''', N''TABLE'', N'''+object_Name(OBJECT_ID(@Identifier))+'''' Select @ExtendedPropertiesBuild= (Select Replace(@ExtendedPropertyExecute,'<value>',Replace(convert(varchar(max),value),'''','''''')) +Coalesce(', '''+Level2Type+''', '''+Level2Name+'''','') from ( select value, null as level2type ,null as level2Name from sys.extended_properties ep WHERE ep.major_ID = OBJECT_ID(@Identifier) AND minor_ID=0 AND class=1 union all select Value,'COLUMN',sys.columns.name from sys.extended_properties ep inner join sys.columns on sys.columns.column_ID=minor_ID and sys.columns.object_ID=major_ID WHERE ep.major_ID = OBJECT_ID(@Identifier) AND class=1 AND minor_ID>0 union all Select Value,'CONSTRAINT',object_name(ep.major_ID) from sys.extended_properties ep inner join sys.objects on object_ID=major_ID where parent_Object_ID=OBJECT_ID(@Identifier) and minor_ID=0 union all Select Value,'INDEX', sys.indexes.name from sys.extended_properties ep inner join sys.indexes on object_ID=major_ID and index_ID=minor_ID where major_ID=OBJECT_ID(@Identifier) and class=7 and minor_ID>0 )f FOR XML PATH(''), TYPE).value('.', 'varchar(max)') SELECT @Script=REPLACE(@Script,'|,|',',')+COALESCE(@PrimaryKeyBuild,'')+' ) ON [PRIMARY]'+ Coalesce(' /* and the extended properties associated with the table and its indexes */'+ @ExtendedPropertiesBuild,'') 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 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
Load comments