Imagine that while developing or testing a SQL Server database, you get an error message mentioning a database object, and you scratch your head, and mutter ‘by the bones of the saints, what is that?’. If it is a small database, then you can glance, maybe, at the browser pane. Once it gets to any size, you’ve got to search the metadata. If you know it is a column than you can look in information_schema.Columns or in sys.columns, but if you don’t know, or if you want to know more about the column, you’re into a certain difficulty. Someone has probably explained what the column does in an extended property. What table or view is it in? what else is in the table?
Finding things in databases isn’t always straightforward. You might think that you can just search the database objects in SQL Server. No, because user types, for example are stored elsewhere, despite being schema-bound. You might want to search in the scheduled jobs too, but there is no way of working out whether the code in a job step relates to a particular database. You might think that the information_schema represented a consistent industry standard, providing a rational logical layer over the seething reality underneath. You might be wrong.
SQL Server, like all RDBMSs, grow in rather strange way, sometimes in response to the esoteric needs of a major customer, the hobbyhorse of a senior manager, or to head off an industry-trend such as NoSQL or Big Data. We have to make retrospective sense of it all.
SQL Server has the concept of a parent object. This roughly corresponds to table, view and module (aka Routine). These parent objects have child objects, which correspond to constraints, and triggers. Internal tables can be either, depending on whether they are queue messages or XML Index nodes that do, or otherwise don’t.
Columns, indexes or parameters aren’t objects. They are treated as ordered lists of attributes to one particular type. Types are schema-based but they aren’t objects. Databases have attributes that aren’t schema-based but which apply to the database as a whole, such as datafiles or dataSpaces. You see some of them in the CREATE database statements in generated scripts.
All this might seem complicated, but I’ve done it for you. I’ve created two SQL Server routines. One is a Table-valued function that list database objects and their paths, and the other is a view that lists the database attributes as well.
You might use the schemaObjects function by itself if you aren’t interested in the database properties, just the database metadata. The obvious way of searching is this. Want to find out where the database processes credit cards?
|
1 2 3 4 |
SELECT Name, path, Comment FROM SchemaObjects () WHERE Name + comment LIKE '%credit%' ORDER BY path; |
…which gives you (in AdventureWorks) the following
The ‘path’ column gives you a reasonable sort order, except for the order of columns. It comes in handy for searching for a specific database object type, or for, say, the foreign key for a particular table.
Here we are trying to find out all the foreign keys in person.BusinessEntityAddress
|
1 2 3 4 |
SELECT Name, path, Comment FROM SchemaObjects () WHERE path LIKE '%.foreign_key_constraint.%' AND path LIKE '%person.table.BusinessEntityAddress%'; |
Maybe you just want to see what is in a table
|
1 2 3 4 |
SELECT Name, path, Comment FROM SchemaObjects () WHERE path LIKE '%humanresources.table.Department.%' ORDER BY path; |
So here is the source of the application. If it is updated, the latest version will be in the Gloop project TheGloopSQLServerDatabaseDocumenter/SearchDatabase.sql
|
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 |
GO CREATE OR ALTER FUNCTION dbo.SchemaObjects ( /** Summary: > Returns all the schema objects, their name, path and so on. Author: Phil Factor Date: Tuesday, 28 June 2022 Examples: - Select * from SchemaObjects() order by path - Select Name, path, Comment from SchemaObjects() where Name+comment like '%credit%' order by path - Select Name, path, Comment from SchemaObjects() where path like '%person.table.businessentity.%' - Select Name, path, Comment from SchemaObjects() where path like '%.foreign_key_constraint.%' Returns: > table **/ ) RETURNS @FoundObjects TABLE (TableOrderType INT, object_id INT, minor_id INT, TheType CHAR(2), SQL_Name NVARCHAR(400), "Name" sysname, "path" NVARCHAR(400), comment NVARCHAR(3870)) AS BEGIN INSERT INTO @FoundObjects (TableOrderType, object_id, minor_id, TheType, SQL_Name, "Name", "path", comment) SELECT --objects -- SQL Prompt formatting off CASE WHEN ob.parent_object_id >0 THEN 10 ELSE 0 END AS TableOrderType, ob.object_id, CASE WHEN ob.parent_object_id >0 THEN 1 ELSE 0 END AS minor_id, ob.type AS TheType, CASE WHEN ob.parent_object_id>0 THEN Object_Schema_Name(ob.parent_object_id) + '.'+Object_Name(ob.parent_object_id)+'.'+ob.name ELSE Object_Schema_Name(ob.object_id)+'.'+ob.name END AS SQL_Name,ob.name AS name, '$.Objects.'+Object_Schema_Name(ob.object_id) + CASE WHEN ob.parent_object_id>0 THEN CASE WHEN pa.type IN ('TF','FN','IF','FS','FT') THEN '.function' WHEN pa.type IN ('P', 'PC','RF','X') THEN '.procedure' WHEN pa.type IN ('U','IT') THEN '.table' WHEN pa.type='SQ' THEN '.queue' ELSE '.'+Lower(pa.type_desc) END +'.' +pa.name+'.' ELSE '.' END+ CASE WHEN ob.type IN ('TF','FN','IF','FS','FT') THEN 'function' WHEN ob.type IN ('P', 'PC','RF','X') THEN 'procedure' WHEN ob.type IN ('U','IT') THEN 'table' WHEN ob.type='SQ' THEN 'queue' ELSE Lower(ob.type_desc) END+'.' --+ CASE WHEN ob.parent_object_id>0 -- THEN Object_Name(ob.parent_object_id) COLLATE DATABASE_DEFAULT+'.' -- ELSE '' -- END + ob.name AS "path", -- SQL Prompt formatting on Coalesce (Convert (NVARCHAR(3870), ep.value), '') AS comment FROM sys.objects ob LEFT OUTER JOIN sys.objects pa ON pa.object_id = ob.parent_object_id LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = ob.object_id AND ep.class = 1 AND ep.minor_id = 0 AND ep.name = 'MS_Description' WHERE ob.is_ms_shipped = 0 AND ob.is_ms_shipped = 0 UNION ALL SELECT 0, 0, xc.xml_collection_id, 'XT', Schema_Name (xc.schema_id) + '.' + xc.name, xc.name, '$.types.' + Schema_Name (xc.schema_id) + '.xml_Schema_collection.' + xc.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') AS comment FROM sys.xml_schema_collections xc LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 10 AND ep.major_id = xc.xml_collection_id AND ep.name = 'MS_Description' WHERE xc.xml_collection_id > 65535 UNION ALL SELECT 0, 0, UT.user_type_id, 'UT', Schema_Name (UT.schema_id) + '.' + UT.name, UT.name, '$.types.' + Schema_Name (UT.schema_id) + '.user_types.' + UT.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') AS comment FROM sys.types UT LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 6 AND ep.major_id = UT.user_type_id AND ep.name = 'MS_Description' WHERE UT.user_type_id <> UT.system_type_id AND Schema_Name (UT.schema_id) <> 'sys'; INSERT INTO @FoundObjects (TableOrderType, object_id, minor_id, TheType, SQL_Name, "Name", "path", comment) SELECT 7, F.object_id AS "OBJECT_ID", ix.index_id AS Minor_id, 'IX', F.SQL_Name + '.' + ix.name, ix.name, F.path + '.indexes.' + ix.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM @FoundObjects F INNER JOIN sys.indexes ix ON F.object_id = ix.object_id LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = ix.object_id AND ep.class = 7 AND ep.name = 'MS_Description' WHERE ix.index_id > 0 AND ep.minor_id = ix.index_id UNION ALL SELECT 2, F.object_id AS "OBJECT_ID", par.parameter_id AS Minor_id, 'PA', F.SQL_Name + '.' + par.name, par.name, F.path + '.parameters.' + par.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM @FoundObjects F INNER JOIN sys.parameters par ON F.object_id = par.object_id LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = par.object_id AND ep.class = 2 AND ep.name = 'MS_Description' AND ep.minor_id = par.parameter_id WHERE par.parameter_id > 0 UNION ALL SELECT 3, F.object_id AS "OBJECT_ID", Col.column_id AS Minor_id, 'CO', F.SQL_Name + '.' + Col.name, Col.name, F.path + '.columns.' + Col.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM @FoundObjects F INNER JOIN sys.columns Col ON F.object_id = Col.object_id LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = Col.object_id AND ep.class = 2 AND ep.name = 'MS_Description' AND ep.minor_id = Col.column_id; RETURN; END; GO |
The view adds the attributes at database level. Of course, there aren’t going to be many of these in a sample database but here goes
Select * from The_Metadata where path like ‘$.Attributes%’
|
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 |
CREATE OR ALTER VIEW The_Metadata /** Summary: > This is a view of all the database-level attributes of the current database Author: Phil Factor Date: Tuesday, 28 June 2022 Database: AdventureWorks Examples: -Select Name, Path, Comment from The_Metadata where name+' '+comment like '%credit%' order by path -Select * from The_Metadata where path like '$.Attributes%' Returns: > table **/ AS SELECT Name, path, comment FROM SchemaObjects () UNION ALL SELECT --schemas sch.name, '$.Attributes.schema.' + sch.name AS Attribute, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.schemas sch LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 3 AND ep.major_id = sch.schema_id AND ep.name = 'MS_Description' WHERE sch.schema_id > 4 AND sch.name NOT LIKE 'DB%' UNION ALL --Database SELECT f.Object, '$.Attributes.Name.' + f.Object, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM (VALUES (Db_Name (), 'database')) f ("Object", "type") LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 0 AND ep.name = 'MS_Description' UNION ALL SELECT --Database Files df.name, '$.Attributes.database_file.' + df.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.database_files df LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 22 AND ep.major_id = df.file_id AND ep.name = 'MS_Description' UNION ALL SELECT --Data Spaces ds.name, '$.Attributes.dataspace.' + ds.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.data_spaces ds LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 20 AND ep.major_id = ds.data_space_id AND ep.name = 'MS_Description' UNION ALL SELECT --USER dp.name, '$.Attributes.database_principal.' + dp.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.database_principals dp LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 4 AND ep.major_id = dp.principal_id AND ep.name = 'MS_Description' WHERE dp.type = 'S' UNION ALL SELECT --PARTITION FUNCTION pf.name, '$.Attributes.partition_function.' + pf.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.partition_functions pf LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 21 AND ep.major_id = pf.function_id AND ep.name = 'MS_Description' UNION ALL SELECT --REMOTE SERVICE BINDING rsb.name, '$.Attributes.remote_service_binding.' + rsb.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.remote_service_bindings rsb LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 18 AND ep.major_id = rsb.remote_service_binding_id AND ep.name = 'MS_Description' UNION ALL SELECT --Route rt.name, '$.Attributes.route.' + rt.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.routes rt LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 19 AND ep.major_id = rt.route_id AND ep.name = 'MS_Description' UNION ALL SELECT --Service sv.name COLLATE DATABASE_DEFAULT, '$.Attributes.service.' + sv.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.services sv LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 17 AND ep.major_id = sv.service_id AND ep.name = 'MS_Description' WHERE sv.name NOT LIKE 'http://schemas.microsoft.com/SQL%' UNION ALL SELECT -- 'CONTRACT' svc.name COLLATE DATABASE_DEFAULT, '$.Attributes.service_contract.' + svc.name COLLATE DATABASE_DEFAULT, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.service_contracts svc LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 16 AND ep.major_id = svc.service_contract_id AND ep.name = 'MS_Description' WHERE svc.name NOT LIKE 'http://schemas.microsoft.com/SQL%' UNION ALL SELECT -- 'MESSAGE TYPE' smt.name, '$.Attributes.message_type.' + smt.name COLLATE DATABASE_DEFAULT, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.service_message_types smt LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 15 AND ep.major_id = smt.message_type_id AND ep.name = 'MS_Description' WHERE smt.name NOT LIKE 'http://schemas.microsoft.com/SQL%' UNION ALL SELECT -- 'assembly' asy.name, '$.Attributes.assembly.' + asy.name COLLATE DATABASE_DEFAULT, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.assemblies asy LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 5 AND ep.major_id = asy.assembly_id AND ep.name = 'MS_Description' WHERE asy.name NOT LIKE 'Microsoft.SqlServer.Types' UNION ALL SELECT --'CERTIFICATE' cer.name, '$.Attributes.certificate.' + cer.name, '' FROM sys.certificates cer UNION ALL SELECT --'ASYMMETRIC KEY' amk.name, '$.Attributes.asymmetric_key.' + amk.name, '' FROM sys.asymmetric_keys amk UNION ALL SELECT --'SYMMETRIC KEY' smk.name, '$.Attributes.symmetric_key.' + smk.name, '' FROM sys.symmetric_keys smk UNION ALL SELECT -- 'PLAN GUIDE' pg.name, '$.Attributes.plan_guide.' + pg.name, Coalesce (Convert (NVARCHAR(3870), ep.value), '') FROM sys.plan_guides pg LEFT OUTER JOIN sys.extended_properties ep ON ep.class = 27 AND ep.major_id = pg.plan_guide_id AND ep.name = 'MS_Description'; GO |
Conclusion
If you are like me, you will tend to do ad-hoc searching via SQL Calls that are written for the immediate purpose. They aren’t always good for their purpose and I weas inspired to write this so as to be able to do path-based searches and to be able to see the comments (extended properties.) It is based various routines I’d needed for another purpose so it is good to pull it all into shape for this function and view.
Load comments