CODEWITHSUNDEEP
sql-serversql-server-2008t-sqldatabase-schema
pencilslate
pencilslate

Reputation: 13068

SQL Query to search schema of all tables

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?

Upvotes: 46

Views: 172411

Answers (8)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

Use this query :

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 '%' + '<ColumnName>' + '%' )
    AND 
    ( t.type = 'U' ) -- Use This To Prevent Selecting System Tables

Upvotes: 1

Paul Belknap
Paul Belknap

Reputation: 21

For me I only have read access to run querys so I need to use this function often here is what I use:

SELECT  *
FROM    INFORMATION_SCHEMA.TABLES
where   TABLES.TABLE_NAME like '%your table name here%'

You can replace .TABLES with .COLUMNS then it would look like this:

 SELECT *
 FROM   INFORMATION_SCHEMA.COLUMNS
 WHERE  columns.COLUMN_NAME like '%your column name here%'

Upvotes: 2

Dwoolk
Dwoolk

Reputation: 1501

You can also try doing this using one of many third party tools that are available for this.

Queries are great for simple searches but if you need to do more manipulation with data, search for references and such this is where you can do a much better job with these.

Also, these come in very handy when some objects are encrypted and you need to search for

I’m using ApexSQL Search which is free but there are also many more (also free) on the market such as Red Gate or SSMS Tool Pack.

Upvotes: 6

Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10384

You do not need to type SQL Query for this in SQL Server 2008.

In SSMS Object Explorer choose Databases or Tables of the required database (if you need to search in one database), open menu View--> Object Explorer Details (alternatively press F7), type %CreatedDate% in Search textbox, ENTER, enjoy

Upvotes: 0

Allbite
Allbite

Reputation: 2487

My favorite...

SELECT objParent.name AS parent, obj.name, col.*
FROM sysobjects obj 
    LEFT JOIN syscolumns col
        ON obj.id = col.id
    LEFT JOIN sysobjects objParent
        ON objParent.id = obj.parent_obj
WHERE col.name LIKE '%Comment%'
   OR obj.name LIKE '%Comment%'

Above I'm searching for "Comment".

Drop the percent signs if you want a direct match.

This searches tables, fields and things like primary key names, constraints, views, etc.

And when you want to search in StoredProcs after monkeying with the tables (and need to make the procs match), use the following...

SELECT name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Comment%'

Hope that helps, I find these two queries to be extremely useful.

Upvotes: 3

Raj More
Raj More

Reputation: 48018

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%'

Upvotes: 62

Tamil.SQL
Tamil.SQL

Reputation: 262

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' )

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294177

  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.

Upvotes: 5

Related Questions