Have you ever wanted a search button in SQL Server Management Studio (SSMS)? Of course, there is a ‘find’ menu item (Cntl F) but it won’t work in the Object Explorer pane of SSMS to find things in your database schema. You also have an object Search feature in SSMS 2008 within the Object Explorer ‘Details’ window, but that only searches the names of objects, not their definitions. It also doesn’t search all objects, not even columns. Obviously, you can find strings within the current query window, but this is only a small fraction of what you actually need when you’re developing a database. You want to search for all your database objects, their names, their definitions and their comments.
How difficult can it be to search through the code (definition) as well as the name of a database object? After all, the object explorer must have a great deal of information about the objects in it. The answer, it seems, is ‘very difficult’.
Summary
Why isn’t it there in SSMS?
I don’t know for sure, but I suspect that the reason there is no way of searching through the code as well as the name of structural metadata by code in SSMS is that it isn’t straightforward within the architecture that Microsoft has chosen to use, without breaking a few rules.
One problem is that there could be quite a few database objects around, such as the CLR functions, check constraints, defaults, default constraints, foreign keys , scalar functions, CLR scalar functions, CLR table-valued functions, inline table-functions, internal tables, stored procedures, CLR stored-procedures, plan guides, primary keys, rules, replication filters, service queues, CLR DML triggers, table functions , indexes, default constraints, table types ,user tables , unique constraints, views, XML Schema collections and extended stored procedures. Quite a few of these objects will have code in them.
The code ,or definition, of objects will be found in user-defined rules, defaults, unencrypted Transact-SQL stored procedures, user-defined Transact-SQL functions, triggers, computed columns, CHECK constraints, views, or system objects such as a system stored procedure. Even if you can search through all the code, you’d also probably need to look at the extended properties too. However, that’s just scraping the surface in terms of what is there or potentially there. .
Another problem is that SSMS is wedded to SMO, which provides an object-oriented programmatic model in place of the real way that SQL Server stores information about its objects such as tables, procedures and columns, and the hierarchical structure of objects. It is clever, it is useful, but it is dead slow to navigate when you’re looking at a large database, and trying to extract the contents of routines.
Why do you need to search your structural metadata?
There are a number of reasons why you might want to peruse your DDL. If you are used to glancing at, or clicking away at, the Object Explorer or using SQL Prompt, then you are probably scanning parts of the metadata. Most searching goes on when you are refactoring or maintaining an existing database that was written by someone else. An especially irksome task is renaming a view, table or a column. A dependency tracker will find the dependent objects but will, even if working perfectly, miss anything that is in dynamic code, embedded in strings. You may think that code only lurks in stored procedures or functions. Oh no. What about constraints, computed columns, defaults, rules, triggers or views? What of code in a CLR that accesses a database table? Code appears in a lot of places. Even if you are familiar with the database, it is easy, for example, to forget about a trigger on a table, miss-spell a column-name or overlook an index. For high-speed programming, the ‘point, click-and-curse’ technique isn’t really an option.
How do you do it?
We’ll go through the various alternative approaches to searching for stuff in your database definition.
Get the free tool
If you don’t want to get immersed in SQL code to do this, SQL Search is free and you don’t even have to give anyone your email address to get it.
This is perfect for the majority of requirements. At the moment, SQL Search is slightly limited in that the current version will search only the main database objects, but promised soon is the ability to search such things as index names and the contents of extended properties. It doesn’t yet entirely replace the requirement for a TSQL-based solution. However, you can see that it does more than the ‘Object-Explorer-Details’ Search of SSMS since it has found the search term in the text or definition of the stored procedure as well as finding it in the name.
Searching the entire build-script
Let’s start with the very simplest, but one of the most reliable methods of searching. You get out your build script. No build script? You generate it. If you like clicking at things, then use SSMS; otherwise use an automated procedure with PowerShell and SMO(remember that you have to regenerate the build script every time someone else makes an alteration). I still use a slightly retro stored procedure with DMO; it works very well for me. Then, you read it into your favorite text editor and use the search facilities in it. This sounds clunky, but if your editor uses RegEx search, then a lot can be done, including the automatic generation of lists. Personally, I am pretty happy with this approach, but it isn’t always convenient.
The use of a query pane with the build script in it is almost as good, but you don’t have the regular expressions, or the performance of a programmers’ text editor. It also doesn’t help with some of the more advanced operations that you might need. I like making lists, with the help of a RegEx string, of the lines, or context, where each match happened. I can then scan them quickly to find a particular occurrence. (see TSQL Regular Expression Workbench for an introduction to RegEx). With a Grep tool, and SQLCMD, you can get lists of lines containing your search string.
The toe-in-the-water. The ‘Help’ system-procedures
There are a number of ‘traditional’ approaches to looking at your structural metadata, but they aren’t going to help much. If you just want a list of the most important objects, then just use…
|
1 |
Execute sp_help --list all objects in the sys.sysobjects table |
…but you will miss out on columns and indexes, and quite a few of the less important objects as well. There are other similar stored procedures, some of which are listed below, which are fine for getting specific information, but not much use for answering questions like ‘Where, in the database, is a date conversion used with a German (104) date format?’ The famous sp_helptext is fine for getting the text of a particular object but no more than that. There is also…
- sp_helpconstraint — all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint
- sp_helpdb — Reports information about a specified database
- sp_helpextendedproc –currently defined extended stored procedures
- sp_helpfile — the physical names and attributes of files associated with the current database
- sp_helpfilegroup -lists file groups
- sp_helpgroup -lists roles
- sp_helpindex -lists indexes, and the columns on which the index is built
- sp_helptext -displays the definition of a routine
- sp_helptrigger list the triggers and their types
Going Standard: Using Information Schema
Edgar Codd’s fourth rule for a relational database management system is that there should be an Active online catalog based on the relational model that is accessible to authorized users by means of their regular query language: This means that users must be able to access the data about the database’s structural metadata (catalog) just as easily as they can access data, and using the same query language that they use to access the database’s data.
The Information_Schema views are a standard way of doing this. You can use them for searching if you don’t want too much. The queries you use for ad-hoc work, such as ‘which index covers this column?’, are best put into templates and dragged/dropped onto your workspace, or held as some form of snippet. Typing them out laboriously was never an option. You can, of course, use a stored procedure if you want to do a general search, or run a utility that queries via ODBC.
However, before we get too excited about the information Schema views, note that they have one or two big drawbacks. The first is that they only store the first 4000 characters of the definition of an object. If you write long stored procedures, you can move on to a later part of this article, or stick cosily to the idea of using a traditional programmers’ text editor as I’ve already described. The second drawback is that if you are interested in anything out of the ordinary, such as triggers or extended properties, I’m afraid you’ll be disappointed. Information Schema are provided only to be compatible with the standard, it seems.
Here is a procedure that does what it can to search your database, using Information schema.
|
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 |
IF EXISTS ( SELECT * FROM information_Schema.routines WHERE specific_name = 'FindStringInInformationSchema' ) DROP PROCEDURE FindStringInInformationSchema go CREATE PROCEDURE FindStringInInformationSchema /** summary: > This finds the string that you specify within the name of many database objects including indexes and parameters of routines. It searches within the text (definition) for every routine. it displays the full path of the database object and the object type This cannot find the text (Definition) or names of triggers, and knows nothing of extended properties example: - code: FindStringInInformationSchema '' --list every object, along with creation date etc - code: FindStringInInformationSchema 'getdate'--find where the string 'getdate' appears! - code: FindStringInInformationSchema 'gender'--find where the string 'gender' appears! returns: > result **/ @SearchString VARCHAR(2000) AS IF CHARINDEX('%', @SearchString) = 0 SELECT @SearchString = '%' + @SearchString + '%' SELECT--report on the routines first, name and definition Specific_Catalog + '.' + Specific_Schema + '.' + Specific_Name AS Qualified_Name, LOWER(Routine_Type) + ' ' + CASE WHEN specific_name LIKE @SearchString THEN 'name' ELSE 'definition' END AS Object_Type FROM information_Schema.routines WHERE specific_name LIKE @SearchString OR routine_Definition LIKE @SearchString UNION ALL SELECT--and search view definitions Table_Catalog + '.' + Table_Schema + '.' + Table_Name, 'view definition' FROM information_Schema.views WHERE View_Definition LIKE @SearchString UNION ALL SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name, LOWER(table_type) + ' Name' FROM information_Schema.tables WHERE Table_name LIKE @SearchString UNION ALL SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + constraint_name, LOWER(Constraint_type) + ' constraint Name' FROM information_Schema.table_constraints WHERE constraint_name LIKE @SearchString UNION ALL SELECT catalog_name + '.' + Schema_name, 'Schema' FROM information_Schema.schemata WHERE schema_name LIKE @SearchString AND schema_name NOT LIKE 'db_%' AND schema_name NOT LIKE 'information_Schema%' UNION ALL SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + column_name, 'TVF Column name' FROM information_Schema.ROUTINE_COLUMNS WHERE column_name LIKE @SearchString UNION ALL SELECT DISTINCT Constraint_Catalog + '.' + constraint_Schema + '.' + constraint_Name, 'Foregn Key constraint' FROM information_Schema.Referential_constraints WHERE constraint_name LIKE @SearchString UNION ALL SELECT DISTINCT Unique_Constraint_Catalog + '.' + Unique_constraint_Schema + '.' + Unique_constraint_Name, 'Unique constraint' FROM information_Schema.Referential_constraints WHERE Unique_constraint_name LIKE @SearchString UNION ALL SELECT Specific_Catalog + '.' + Specific_Schema + '.' + Specific_Name + '(' + Parameter_name + ')', 'routine parameter' FROM information_schema.parameters WHERE parameter_name <> '' AND parameter_name LIKE @SearchString UNION ALL SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + Column_Name, 'column Name' FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE @SearchString UNION ALL SELECT Table_Catalog + '.' + Table_Schema + '.' + Table_Name + '.' + Column_Name + '.default', 'default constraint text' FROM INFORMATION_SCHEMA.COLUMNS WHERE column_default LIKE @SearchString |
…Which will give you something like this….
Learning patience: Using SMO
The most potentially powerful means of searching your database objects is by use of Server Management Objects (SMO). You can get a huge range of database and server objects with SMO, as long as you don’t mind waiting, and if you like coding in a .NET language. The problems come when you try to tackle a large database. Because you have to walk the hierarchy of SMOs objects, rather than do a set-oriented operation, you are soon in trouble if you have a lot of tables and columns. SMO provides a logical and consistent interface into a database, but it does not represent the reality of the way that this information is actually stored in the database.
SMO comes into its own as a way of quickly finding out how to access a ‘difficult’ database object. If you use SMO, and run Profiler at the same time to see what SQL is being executed, you can quickly achieve SQL MegaStar status in your development team.
Using the Object Catalog views
I love the Object Catalog views. Even Edgar Codd might have smiled. I wouldn’t suggest using them ‘raw’, as they are not as ‘unwrapped’ or denormalised as the Information Schema views. Really, the only way to survive is to have all the information tucked into a stored procedure that you’d call up to find the things you wanted. Here is the routine I use to search through as much as possible of the structure of databases. I’ve written it like a Lego tower, with a series of UNION ALLs so as to allow you to break it up and create your own Lego tower.
|
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 |
IF EXISTS ( SELECT * FROM information_Schema.routines WHERE specific_name = 'FindString' ) DROP PROCEDURE FindString go CREATE PROCEDURE FindString /** summary: > This finds the string that you specify withing the name of every database object including indices, agent jobs, extended properties and parameters of routines. It searches within the text (definition) for every routine, and within the value of every extended property. it displays the full path of the database object, the object type the dates of creation and modification (sometimes that of the parent if this information isn't available) as well as the name of the parent of the object. example: - code: findstring '' --list every object, along with creation date etc example: - code: findString 'GetDate'--find where the string 'getdate' appears! example: - code: findstring 'b[aeiou]t'--find but, bot,bit,bet and bat! returns: > Qualified_Name Varchar This is the fully-qualified name of the object so that you know where it lurks. Sometimes, the path is a bit artificial, but it should be obvious how the convention works. Object_Type Varchar A description of the type of object the string was found in. 'Definition' refers to the code within the routine. created DateTime The date that either the object or its' parent was created. Last_Modified DateTime The date that either the object or its' parent was last modified Parent Varchar The name of the parent of the object if known **/ @SearchString VARCHAR(2000) AS ------------------------------------------------------------------------------------- IF CHARINDEX('%', @SearchString) = 0 --if he hasn't done in the SQL wildcard format. SELECT @SearchString = '%' + @SearchString + '%' --add it SELECT --firstly, we'll search the names of the basic objects DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.' + COALESCE(p.name + '.', '') + s.name AS [Qualified_Name], replace(SUBSTRING(v.name, 5, 31),'cns','constraint') + ' name' AS Object_Type, s.create_date AS 'Created', s.modify_date AS 'Last_Modified', COALESCE(p.name, '-') AS 'parent' FROM sys.objects S --to get the objects LEFT OUTER JOIN master.dbo.spt_values v--to get the type of object ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T' LEFT OUTER JOIN sys.objects p --to get any parent object ON s.parent_Object_ID = p.[object_ID] WHERE s.name LIKE @SearchString--string you want to search for AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%' UNION ALL SELECT--and search all the names of the columns too DB_NAME() + '.' + Object_Schema_name(s.object_ID) + '.' + '.' + s.name + '.' + c.name AS [name], 'Column name' AS [object_type], s.create_date AS 'created', s.modify_date AS 'Last Modified', COALESCE(s.name, '-') AS 'parent' FROM sys.columns c INNER JOIN sys.objects S --get table data ON c.object_ID = s.object_ID WHERE c.name LIKE @SearchString--string you want to search for AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%' UNION ALL SELECT--and search all the definitions of the computed columns too DB_NAME() + '.' + Object_Schema_name(s.object_ID) + '.' + s.name + ',' + c.name AS [name], 'computed Column definition' AS [object_type], s.create_date AS 'created', s.modify_date AS 'Last Modified', COALESCE(s.name, '-') AS 'parent' FROM sys.computed_columns c INNER JOIN sys.objects S ON c.object_ID = s.object_ID WHERE c.definition LIKE @SearchString--string you want to search for AND Object_Schema_name(s.object_ID) NOT LIKE 'sys$' UNION ALL --now search the XML schema collection names SELECT DB_NAME() + '.' + name, 'XML Schema Collection name', create_date AS 'created', modify_date AS 'Last Modified', '-' AS 'parent' FROM sys.xml_schema_collections WHERE name LIKE @SearchString UNION ALL --and now search the names of the DDL triggers (they arent in sys.objects) SELECT DB_NAME() + '.' + name, LOWER(type_desc) COLLATE database_default, create_date AS 'created', modify_date AS 'Last Modified', '-' AS 'parent' FROM sys.triggers WHERE name LIKE @SearchString--string you want to search for AND parent_class = 0--only DDL triggers UNION ALL --and search the names of all the indexes SELECT DB_NAME() + '.' + Object_Schema_name(p.object_ID) + '.' + p.name + '.' + i.name, LOWER(i.type_desc) + ' index name' COLLATE database_default, create_date AS 'created', modify_date AS 'Last Modified', p.name AS 'parent' FROM sys.indexes i INNER JOIN sys.objects p ON i.object_ID = p.object_ID WHERE i.name LIKE @SearchString--string you want to search for AND Object_Schema_name(i.object_ID) NOT LIKE 'sys%' AND is_primary_key = 0 AND i.type_desc <> 'HEAP' UNION ALL--and we want to know the parameters to the routines SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + +s.name + '(' + pa.name + ')' AS [name], SUBSTRING(v.name, 5, 31) + ' parameter name' AS [object_type], s.create_date AS 'created', s.modify_date AS 'Last Modified', COALESCE(s.name, '-') AS 'parent' FROM sys.parameters pa INNER JOIN sys.objects S --to get the objects ON pa.object_ID = S.object_ID LEFT OUTER JOIN master.dbo.spt_values v ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T' WHERE pa.name <> '' AND pa.name LIKE @SearchString--string you want to search for AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%' UNION ALL--and the text (definition) of the default constraints SELECT DB_NAME() + '.' + Object_Schema_name(d.parent_object_id) + '.' + OBJECT_NAME(d.parent_object_id) + '.' +c.name+ '.' + d.name, 'default constraint definition', NULL AS 'created', NULL AS 'Last Modified', OBJECT_NAME(d.parent_object_id) AS 'parent' FROM sys.default_constraints d INNER JOIN sys.columns c ON d.parent_column_id = c.column_id and d.parent_object_ID=c.object_ID WHERE definition LIKE @SearchString UNION ALL --the text of other table objects SELECT DB_NAME() + '.' + Object_Schema_name(p.object_id) + '.' + OBJECT_NAME(p.object_id), SUBSTRING(v.name, 5, 31) + ' definition' AS [object_type], p.create_date AS 'created', p.modify_date AS 'Last Modified', '-' AS 'parent' FROM sys.sql_modules m INNER JOIN sys.objects p ON m.object_ID = p.object_ID LEFT OUTER JOIN master.dbo.spt_values v ON p.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T' WHERE definition LIKE @SearchString UNION ALL--and the text of the check constraints SELECT DB_NAME() + '.' + Object_Schema_name(d.parent_object_id) + '.' + OBJECT_NAME(d.parent_object_id) + '.' + c.name + '.' + d.name, 'check constraint definition', create_date AS 'created', modify_date AS 'Last Modified', OBJECT_NAME(d.parent_object_id) + '.' + c.name AS 'parent' FROM sys.check_constraints d INNER JOIN sys.columns c ON d.parent_column_id = c.column_id AND d.parent_object_ID = c.Object_ID WHERE definition LIKE @SearchString UNION ALL --what about the extended properties? Let's check them SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.' + COALESCE(p.name + '.', '') + s.name AS [name], SUBSTRING(v.name, 5, 31) + ' Extended property' AS [object_type], s.create_date AS 'created', s.modify_date AS 'Last Modified', COALESCE(p.name, '-') AS 'parent' FROM sys.extended_properties ep INNER JOIN sys.objects s ON s.object_ID = ep.major_ID LEFT OUTER JOIN master.dbo.spt_values v ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T' LEFT OUTER JOIN sys.objects p --to get any parent object ON s.parent_Object_ID = p.[object_ID] WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString--string you want to search for AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%' AND class = 1 AND minor_ID = 0--object UNION ALL --and extended property comments on columns SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.' + s.name + '.' + c.name AS [name], 'Column Extended property' AS [object_type], s.create_date AS 'created', s.modify_date AS 'Last Modified', COALESCE(c.name, '-') AS 'parent' FROM sys.extended_properties ep INNER JOIN sys.objects s ON s.object_ID = ep.major_ID LEFT OUTER JOIN sys.columns c ON c.column_ID = minor_ID AND c.object_ID = ep.major_ID WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%' AND class = 1 AND minor_ID <> 0--object UNION ALL --and we need to search the properties of parameters to routines SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.' + s.name + '(' + p.name + ')' AS [name], SUBSTRING(v.name, 5, 31) + ' parameter x Property' AS [object_type], s.create_date AS 'created', s.modify_date AS 'Last Modified', COALESCE(s.name, '-') AS 'parent' FROM sys.extended_properties ep INNER JOIN sys.objects s ON s.object_ID = ep.major_ID LEFT OUTER JOIN master.dbo.spt_values v ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T' LEFT OUTER JOIN sys.parameters p ON p.parameter_ID = minor_ID AND p.object_ID = ep.major_ID WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%' AND class = 2--it is a parameter UNION ALL --and we need to search the properties of indexes SELECT DB_NAME() + '.' + Object_Schema_name(s.[object_ID]) + '.' + s.name + '.' + i.name AS [name], SUBSTRING(v.name, 5, 31) + ' index' AS [object_type], s.create_date AS 'created', s.modify_date AS 'Last Modified', COALESCE(s.name, '-') AS 'parent' FROM sys.extended_properties ep INNER JOIN sys.objects s ON s.object_ID = ep.major_ID LEFT OUTER JOIN master.dbo.spt_values v ON s.type = SUBSTRING(v.name, 1, 2) COLLATE database_default AND v.type = 'O9T' LEFT OUTER JOIN sys.indexes i ON i.index_ID = minor_ID AND i.object_ID = ep.major_ID WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString AND Object_Schema_name(s.object_ID) NOT LIKE 'sys%' AND class = 7--it is an index UNION ALL -- we get XML Schema Collection extended properties SELECT DB_NAME() + '.' + sc.name + '.' + xsc.name + '.' + ep.name AS [name], 'XML Schema Collection X property', xsc.create_date AS 'created', xsc.modify_date AS 'Last Modified', xsc.name AS 'parent' FROM sys.extended_properties ep INNER JOIN sys.xml_schema_collections xsc ON xsc.xml_collection_id = ep.major_id INNER JOIN sys.schemas sc ON sc.schema_ID = xsc.schema_ID WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString AND class = 10--all the other properties UNION ALL --now scoop up all other extended properties (loadsa comments) SELECT DB_NAME() + '.' + sc.name + '.' + ep.name AS [name], LOWER(class_desc) + ' X Property', NULL AS 'created', NULL AS 'Last Modified', '-' AS 'parent' FROM sys.extended_properties ep INNER JOIN sys.schemas sc ON sc.schema_ID = ep.major_ID WHERE CONVERT(VARCHAR(MAX), ep.value) LIKE @SearchString AND class NOT IN (1, 2, 7, 10)--all the other properties UNION ALL --names of CLR assemblies SELECT DB_NAME() + '.' + name, 'CLR Assembly', create_date AS 'created', modify_date AS 'Last Modified', '-' AS 'parent' FROM sys.assemblies WHERE clr_name LIKE @SearchString OR name LIKE @SearchString union all --almost done. We do the agent jobs too here SELECT 'Agent' + '.' + DB_NAME() + '.' + [name] + '.' + step_name COLLATE database_default , 'Agent ' + CASE TypeOfHit WHEN 1 THEN 'job description' WHEN 2 THEN 'job name' WHEN 3 THEN 'step name' WHEN 4 THEN 'job contents' ELSE 'impossible' END, date_created, date_modified, [name] COLLATE database_default FROM (SELECT Job.name, step_name, date_created, Date_modified, CASE WHEN Job.Description LIKE @SearchString COLLATE database_default THEN 1 --job description WHEN Job.name LIKE @SearchString COLLATE database_default THEN 2--jpb name WHEN step_name LIKE @SearchString COLLATE database_default THEN 3--step name WHEN Step.command LIKE @SearchString COLLATE database_default THEN 4 --job contents ELSE 0 END AS TypeOfHit FROM MSDB.dbo.sysJobs Job INNER JOIN MSDB.dbo.sysJobSteps Step ON Job.Job_Id = Step.Job_Id WHERE Database_name LIKE DB_NAME() COLLATE database_default ) Oursteps WHERE typeOfHit > 0 |
…which will give you something like…
Using System Tables
Now, you’ll see that this will only work with SQL Server 2005 or 2008. This shouldn’t stop you dead in your tracks if you are stuck on SQL Server 2000 or older, because there are still ways to do it. Instead of using the catalog views, you have to use the system tables. The mapping between the System tables and the System catalog views are all listed for you on Books on Line here. The big problem is that the code of each routine is stored in as many NVARCHAR(4000) chunks as is required to hold the entire definition, which complicates the code. A solution that I use for searching through the definitions of routines in SQL Server 2000 database is given here http://www.simple-talk.com/community/blogs/philfactor/archive/2006/06/03/854.aspx but I wouldn’t want to bulk up this article with a SQL Server 2000 version of the FindString code.
In Conclusion
I use all the methods I’ve described for perusing the structures in databases. If I want quick information in the course of writing a database routine, I use SQL Search. When I’m doing some intensive refactoring, I’ll use the full Build script, but always in a Programmers Text Editor rather than SSMS. (I once hit Execute instead of ‘open file’, and deleted a database). I use Information Schema Views wherever possible as they are reasonably future-proof and open-standard. I use Object Catalog views when I can’t get what I want from Information_Schema views, and use System tables when I’m having to use older versions of SQL Server.
I realise, with a guilty start, that this article has been slightly self-centred in that I’ve talked about my own preferences and work practices. If you use a different approach to searching your database DDL scripts that I haven’t mentioned, then I’d be fascinated to hear about in it a comment on this article.
Load comments