{"id":94701,"date":"2022-06-29T15:46:56","date_gmt":"2022-06-29T15:46:56","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=94701"},"modified":"2022-06-29T15:46:56","modified_gmt":"2022-06-29T15:46:56","slug":"searching-through-a-database-via-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/searching-through-a-database-via-sql\/","title":{"rendered":"Searching through a database via SQL"},"content":{"rendered":"

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 \u2018by the bones of the saints, what is that?\u2019. If it is a small database, then you can glance, maybe, at the browser pane. Once it gets to any size, you\u2019ve got to search the metadata. If you know it is a column than you can look in information_schema.Columns<\/strong> or in sys.columns<\/strong>, but if you don\u2019t know, or if you want to know more about the column, you\u2019re 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?<\/p>\n

Finding things in databases isn\u2019t 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.<\/p>\n

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

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

Columns, indexes or parameters aren\u2019t objects. They are treated as ordered lists of attributes to one particular type. Types are schema-based but they aren\u2019t objects. Databases have attributes that aren\u2019t 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.<\/p>\n

All this might seem complicated, but I\u2019ve done it for you. I\u2019ve 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.<\/p>\n

You might use the schemaObjects function by itself if you aren\u2019t 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?<\/p>\n

SELECT Name, path, Comment\r\n  FROM SchemaObjects ()\r\n  WHERE Name + comment LIKE '%credit%'\r\n  ORDER BY path;\r\n<\/pre>\n
 <\/p>\n
\u2026which gives you (in AdventureWorks) the following<\/p>\n
\"Graphical<\/p>\n
The \u2018path\u2019 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.<\/p>\n
Here we are trying to find out all the foreign keys in person.BusinessEntityAddress<\/p>\n
SELECT Name, path, Comment\r\n  FROM SchemaObjects ()\r\n  WHERE path LIKE '%.foreign_key_constraint.%' \r\n  AND path LIKE '%person.table.BusinessEntityAddress%';\r\n<\/pre>\n
 <\/p>\n
<\/p>\n
Maybe you just want to see what is in a table<\/p>\n
SELECT Name, path, Comment\r\n  FROM SchemaObjects ()\r\n  WHERE path LIKE '%humanresources.table.Department.%'\r\n  ORDER BY path;\r\n<\/pre>\n
 <\/p>\n
\"Graphical<\/p>\n
So here is the source of the application. If it is updated, the latest version will be in the Gloop project TheGloopSQLServerDatabaseDocumenter<\/a>\/SearchDatabase.sql<\/p>\n
GO\r\nCREATE OR ALTER FUNCTION dbo.SchemaObjects\r\n  (\r\n\/**\r\nSummary: >\r\n  Returns all the schema objects, their name, path and so on.\r\nAuthor: Phil Factor\r\nDate: Tuesday, 28 June 2022\r\nExamples:\r\n   - Select * from SchemaObjects() order by path\r\n   - Select  Name, path, Comment from SchemaObjects() where Name+comment like '%credit%' order by path\r\n   - Select  Name, path, Comment from SchemaObjects() where path like '%person.table.businessentity.%'\r\n   - Select  Name, path, Comment from SchemaObjects() where path like '%.foreign_key_constraint.%'\r\nReturns: >\r\n  table \r\n**\/\r\n) RETURNS @FoundObjects TABLE\r\n  (TableOrderType INT,\r\n   object_id INT,\r\n   minor_id INT,\r\n   TheType CHAR(2),\r\n   SQL_Name NVARCHAR(400),\r\n   \"Name\" sysname,\r\n   \"path\" NVARCHAR(400),\r\n   comment NVARCHAR(3870))\r\nAS\r\n  BEGIN\r\n    INSERT INTO @FoundObjects\r\n      (TableOrderType, object_id, minor_id, TheType, SQL_Name, \"Name\",\r\n       \"path\", comment)\r\n    SELECT --objects \r\n-- SQL Prompt formatting off\r\n\t   CASE WHEN ob.parent_object_id >0 THEN 10 ELSE 0 END AS TableOrderType,\r\n\t\t ob.object_id, CASE WHEN ob.parent_object_id >0 THEN 1 ELSE 0 END AS minor_id, ob.type AS TheType,\r\n\t\t CASE WHEN ob.parent_object_id>0 \r\n\t\t THEN Object_Schema_Name(ob.parent_object_id)\r\n\t\t + '.'+Object_Name(ob.parent_object_id)+'.'+ob.name \r\n\t\t ELSE Object_Schema_Name(ob.object_id)+'.'+ob.name \r\n\t   END AS SQL_Name,ob.name AS name,\r\n\t\t '$.Objects.'+Object_Schema_Name(ob.object_id) +\r\n\t   CASE WHEN ob.parent_object_id>0 THEN \r\n\t\t\tCASE WHEN pa.type IN ('TF','FN','IF','FS','FT') THEN '.function'\r\n\t\t\t\tWHEN pa.type IN ('P', 'PC','RF','X') THEN '.procedure' \r\n\t\t\t\tWHEN pa.type IN ('U','IT') THEN '.table'\r\n\t\t\t\tWHEN pa.type='SQ' THEN '.queue'\r\n\t\t\t\tELSE '.'+Lower(pa.type_desc)\r\n\t\t\tEND +'.' +pa.name+'.' ELSE '.' \r\n\t   END+\r\n\t\t CASE WHEN ob.type IN ('TF','FN','IF','FS','FT') THEN 'function'\r\n\t\t\t  WHEN ob.type IN ('P', 'PC','RF','X') THEN 'procedure' \r\n\t\t\t  WHEN ob.type IN ('U','IT') THEN 'table'\r\n\t\t\t  WHEN ob.type='SQ' THEN 'queue'\r\n\t\t\t  ELSE \r\n\t\t\t  Lower(ob.type_desc)\r\n\t\t END+'.'\r\n\t\t --+ CASE WHEN ob.parent_object_id>0 \r\n\t\t--\t THEN Object_Name(ob.parent_object_id) COLLATE DATABASE_DEFAULT+'.'\r\n\t\t--\t ELSE '' \r\n\t\t -- END \r\n\t\t+ ob.name AS \"path\",\r\n-- SQL Prompt formatting on\r\n      Coalesce (Convert (NVARCHAR(3870), ep.value), '') AS comment\r\n      FROM\r\n      sys.objects ob\r\n        LEFT OUTER JOIN sys.objects pa\r\n          ON pa.object_id = ob.parent_object_id\r\n        LEFT OUTER JOIN sys.extended_properties ep\r\n          ON ep.major_id = ob.object_id\r\n         AND ep.class = 1\r\n         AND ep.minor_id = 0\r\n         AND ep.name = 'MS_Description'\r\n      WHERE\r\n      ob.is_ms_shipped = 0 AND ob.is_ms_shipped = 0\r\n    UNION ALL\r\n    SELECT 0, 0, xc.xml_collection_id, 'XT',\r\n           Schema_Name (xc.schema_id) + '.' + xc.name, xc.name,\r\n           '$.types.' + Schema_Name (xc.schema_id)\r\n           + '.xml_Schema_collection.' + xc.name,\r\n           Coalesce (Convert (NVARCHAR(3870), ep.value), '') AS comment\r\n      FROM\r\n      sys.xml_schema_collections xc\r\n        LEFT OUTER JOIN sys.extended_properties ep\r\n          ON ep.class = 10\r\n         AND ep.major_id = xc.xml_collection_id\r\n         AND ep.name = 'MS_Description'\r\n      WHERE xc.xml_collection_id > 65535\r\n    UNION ALL\r\n    SELECT 0, 0, UT.user_type_id, 'UT',\r\n           Schema_Name (UT.schema_id) + '.' + UT.name, UT.name,\r\n           '$.types.' + Schema_Name (UT.schema_id) + '.user_types.' + UT.name,\r\n           Coalesce (Convert (NVARCHAR(3870), ep.value), '') AS comment\r\n      FROM\r\n      sys.types UT\r\n        LEFT OUTER JOIN sys.extended_properties ep\r\n          ON ep.class = 6\r\n         AND ep.major_id = UT.user_type_id\r\n         AND ep.name = 'MS_Description'\r\n      WHERE\r\n      UT.user_type_id <> UT.system_type_id\r\n  AND Schema_Name (UT.schema_id) <> 'sys';\r\n\r\n\r\n    INSERT INTO @FoundObjects\r\n      (TableOrderType, object_id, minor_id, TheType, SQL_Name, \"Name\",\r\n       \"path\", comment)\r\n    SELECT 7, F.object_id AS \"OBJECT_ID\", ix.index_id AS Minor_id, 'IX',\r\n           F.SQL_Name + '.' + ix.name, ix.name,\r\n           F.path + '.indexes.' + ix.name,\r\n           Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n      FROM\r\n      @FoundObjects F\r\n        INNER JOIN sys.indexes ix\r\n          ON F.object_id = ix.object_id\r\n        LEFT OUTER JOIN sys.extended_properties ep\r\n          ON ep.major_id = ix.object_id\r\n         AND ep.class = 7\r\n         AND ep.name = 'MS_Description'\r\n      WHERE\r\n      ix.index_id > 0 AND ep.minor_id = ix.index_id\r\n    UNION ALL\r\n    SELECT 2, F.object_id AS \"OBJECT_ID\", par.parameter_id AS Minor_id, 'PA',\r\n           F.SQL_Name + '.' + par.name, par.name,\r\n           F.path + '.parameters.' + par.name,\r\n           Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n      FROM\r\n      @FoundObjects F\r\n        INNER JOIN sys.parameters par\r\n          ON F.object_id = par.object_id\r\n        LEFT OUTER JOIN sys.extended_properties ep\r\n          ON ep.major_id = par.object_id\r\n         AND ep.class = 2\r\n         AND ep.name = 'MS_Description'\r\n         AND ep.minor_id = par.parameter_id\r\n      WHERE par.parameter_id > 0\r\n    UNION ALL\r\n    SELECT 3, F.object_id AS \"OBJECT_ID\", Col.column_id AS Minor_id, 'CO',\r\n           F.SQL_Name + '.' + Col.name, Col.name,\r\n           F.path + '.columns.' + Col.name,\r\n           Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n      FROM\r\n      @FoundObjects F\r\n        INNER JOIN sys.columns Col\r\n          ON F.object_id = Col.object_id\r\n        LEFT OUTER JOIN sys.extended_properties ep\r\n          ON ep.major_id = Col.object_id\r\n         AND ep.class = 2\r\n         AND ep.name = 'MS_Description'\r\n         AND ep.minor_id = Col.column_id;\r\n    RETURN;\r\n  END;\r\nGO\r\n<\/pre>\n
 <\/p>\n
The view adds the attributes at database level. Of course, there aren\u2019t going to be many of these in a sample database but here goes<\/p>\n
Select * from The_Metadata where path like ‘$.Attributes%’<\/p>\n
\"Graphical<\/p>\n
CREATE OR ALTER VIEW The_Metadata\r\n\/**\r\nSummary: >\r\n  This is a view of all the database-level attributes \r\n  of the current database\r\nAuthor: Phil Factor\r\nDate: Tuesday, 28 June 2022\r\nDatabase: AdventureWorks\r\nExamples:\r\n   -Select Name, Path, Comment from The_Metadata where name+' '+comment like '%credit%' order by path\r\n   -Select * from The_Metadata where path like '$.Attributes%' \r\nReturns: >\r\n  table\r\n**\/\r\nAS\r\n  SELECT Name, path, comment FROM SchemaObjects ()\r\n  UNION ALL\r\n  SELECT --schemas\r\n    sch.name, '$.Attributes.schema.' + sch.name AS Attribute,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.schemas sch\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 3\r\n       AND ep.major_id = sch.schema_id\r\n       AND ep.name = 'MS_Description'\r\n    WHERE\r\n    sch.schema_id > 4 AND sch.name NOT LIKE 'DB%'\r\n  UNION ALL --Database \r\n  SELECT f.Object, '$.Attributes.Name.' + f.Object,\r\n         Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM (VALUES (Db_Name (), 'database')) f (\"Object\", \"type\")\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 0 AND ep.name = 'MS_Description'\r\n  UNION ALL\r\n  SELECT --Database Files\r\n    df.name, '$.Attributes.database_file.' + df.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.database_files df\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 22\r\n       AND ep.major_id = df.file_id\r\n       AND ep.name = 'MS_Description'\r\n  UNION ALL\r\n  SELECT --Data Spaces\r\n    ds.name, '$.Attributes.dataspace.' + ds.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.data_spaces ds\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 20\r\n       AND ep.major_id = ds.data_space_id\r\n       AND ep.name = 'MS_Description'\r\n  UNION ALL\r\n  SELECT --USER\r\n    dp.name, '$.Attributes.database_principal.' + dp.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.database_principals dp\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 4\r\n       AND ep.major_id = dp.principal_id\r\n       AND ep.name = 'MS_Description'\r\n    WHERE dp.type = 'S'\r\n  UNION ALL\r\n  SELECT --PARTITION FUNCTION\r\n    pf.name, '$.Attributes.partition_function.' + pf.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.partition_functions pf\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 21\r\n       AND ep.major_id = pf.function_id\r\n       AND ep.name = 'MS_Description'\r\n  UNION ALL\r\n  SELECT --REMOTE SERVICE BINDING\r\n    rsb.name, '$.Attributes.remote_service_binding.' + rsb.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.remote_service_bindings rsb\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 18\r\n       AND ep.major_id = rsb.remote_service_binding_id\r\n       AND ep.name = 'MS_Description'\r\n  UNION ALL\r\n  SELECT --Route\r\n    rt.name, '$.Attributes.route.' + rt.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.routes rt\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 19\r\n       AND ep.major_id = rt.route_id\r\n       AND ep.name = 'MS_Description'\r\n  UNION ALL\r\n  SELECT --Service\r\n    sv.name COLLATE DATABASE_DEFAULT, '$.Attributes.service.' + sv.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.services sv\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 17\r\n       AND ep.major_id = sv.service_id\r\n       AND ep.name = 'MS_Description'\r\n    WHERE sv.name NOT LIKE 'http:\/\/schemas.microsoft.com\/SQL%'\r\n  UNION ALL\r\n  SELECT -- 'CONTRACT'\r\n    svc.name COLLATE DATABASE_DEFAULT,\r\n    '$.Attributes.service_contract.' + svc.name COLLATE DATABASE_DEFAULT,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.service_contracts svc\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 16\r\n       AND ep.major_id = svc.service_contract_id\r\n       AND ep.name = 'MS_Description'\r\n    WHERE svc.name NOT LIKE 'http:\/\/schemas.microsoft.com\/SQL%'\r\n  UNION ALL\r\n  SELECT -- 'MESSAGE TYPE'\r\n    smt.name,\r\n    '$.Attributes.message_type.' + smt.name COLLATE DATABASE_DEFAULT,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.service_message_types smt\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 15\r\n       AND ep.major_id = smt.message_type_id\r\n       AND ep.name = 'MS_Description'\r\n    WHERE smt.name NOT LIKE 'http:\/\/schemas.microsoft.com\/SQL%'\r\n  UNION ALL\r\n  SELECT -- 'assembly'\r\n    asy.name, '$.Attributes.assembly.' + asy.name COLLATE DATABASE_DEFAULT,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.assemblies asy\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 5\r\n       AND ep.major_id = asy.assembly_id\r\n       AND ep.name = 'MS_Description'\r\n    WHERE asy.name NOT LIKE 'Microsoft.SqlServer.Types'\r\n  UNION ALL\r\n  SELECT --'CERTIFICATE'\r\n    cer.name, '$.Attributes.certificate.' + cer.name, ''\r\n    FROM sys.certificates cer\r\n  UNION ALL\r\n  SELECT --'ASYMMETRIC KEY'\r\n    amk.name, '$.Attributes.asymmetric_key.' + amk.name, ''\r\n    FROM sys.asymmetric_keys amk\r\n  UNION ALL\r\n  SELECT --'SYMMETRIC KEY'\r\n    smk.name, '$.Attributes.symmetric_key.' + smk.name, ''\r\n    FROM sys.symmetric_keys smk\r\n  UNION ALL\r\n  SELECT -- 'PLAN GUIDE' \r\n    pg.name, '$.Attributes.plan_guide.' + pg.name,\r\n    Coalesce (Convert (NVARCHAR(3870), ep.value), '')\r\n    FROM\r\n    sys.plan_guides pg\r\n      LEFT OUTER JOIN sys.extended_properties ep\r\n        ON ep.class = 27\r\n       AND ep.major_id = pg.plan_guide_id\r\n       AND ep.name = 'MS_Description';\r\nGO\r\n<\/pre>\n
 <\/p>\n
Conclusion<\/h1>\n
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\u2019t 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\u2019d needed for another purpose so it is good to pull it all into shape for this function and view.<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u2018by the bones of the saints, what is that?\u2019. If it is a small database, then you can glance, maybe, at the browser pane. Once it gets to any……<\/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":[2],"tags":[],"coauthors":[6813],"class_list":["post-94701","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94701","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=94701"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94701\/revisions"}],"predecessor-version":[{"id":94708,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94701\/revisions\/94708"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94701"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}