Video Screencast Help
Symantec Appoints Michael A. Brown CEO. Learn more.
Endpoint Management Community Blog

How to Search Tables and Views for columns

Created: 18 Sep 2012
jasonfo's picture
+1 1 Vote
Login to vote

If you have ever needed to create a report but dont know what table you are looking for this might help you out. In my case I had to find something that had the "Publisher".

 To look for columns in view...

USE
Symantec_CMDB
SELECT
V.name AS View_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.views AS v
INNER JOIN sys.columns c ON v.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Publisher%'
ORDER BY schema_name, View_name;

To look for columns in tables...

USE
Symantec_CMDB
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Publisher%'
ORDER BY schema_name, table_name;