SQL Query to search schema of all tables

user_144754 0
Viewed 144578

I am working on a SQL Server 2008 Db that has many tables in it (around 200). Many of these tables contain a field by the name "CreatedDate". I am trying to identify all the table schema with this particular field.

Is there a SQL query to do this?

database-schema sql-server-2008 tsql sql-server
8 Answers

I would query the information_schema - this has views that are much more readable than the underlying tables.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%create%'
user_1090350 
  select object_name(c.object_id) as table_name
    , schema_name(t.schema_id) as schema_name
    from sys.columns c
    join sys.tables t on c.object_id = t.object_id
     where c.name=N'CreatedDate';

It gets a little more complicated if you want alsoother table properties, but you'll refer to the object catalog views like sys.tables, sys.columns etc.

user_1059290

Same thing but in ANSI way

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'CreateDate' )

user_1550440
Related