SQL Query to search schema of all tables

Question

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

User · Accepted Answer

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 · Answer

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

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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         lt ColumnName gt               AND        t type    U    -- Use This To Prevent Selecting System Tables

[sql-server] SQL Query to search schema of all tables

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to database-schema