SQL SERVER – Query to Find Column From All Tables of Database

One question came up just a day ago while I was writing SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN.

How many tables in database AdventureWorks have column name like ‘EmployeeID’?

It was quite an interesting question and I thought if there are scripts which can do this would be great. I quickly wrote down following script which will go return all the tables containing specific column along with their schema name.

USE AdventureWorks
GO
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 '%EmployeeID%'
ORDER BY schema_name, table_name;

In above query replace EmployeeID with any other column name.

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
ORDER BY schema_name, table_name;

If you want to find all the column name from your database run following script. You can down any condition in WHERE clause to get desired result.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

143 thoughts on “SQL SERVER – Query to Find Column From All Tables of Database

  1. We may also get same information through following script
    ——————————————————————–
    USE AdventureWorks
    GO
    SELECT Table_Schema, Table_Name, Column_Name, Data_Type
    FROM information_schema.columns
    WHERE table_name in ( select name from adventureworks..sysobjects
    where xtype = ‘U’ )
    and column_name like ‘%EmployeeID%’
    order by table_schema, table_name
    ——————————————————————-
    Take crae

  2. For me both solution don’t work :(

    I receive this error :
    Msg 195, Level 15, State 10, Line 2
    ‘SCHEMA_NAME’ is not a recognized function name.

    I’m not DBA, than no access to the server but I can tell you it’s SQL Server 2005 ENTR + SP2 on my side I have “Microsoft SQL Server Management Studio”

  3. I am looking for a way to automatically document all the tables in my database as well as the relationships between these tables. It seems that I could start to do this via querying sys.tables and information_schema.columns, but before I started down this path, I was wondering if you could recommend a best practice for accomplishing this or recommend a software program that does this well.

    Thanks in advance for your help this, and keep up the good work with the site. It is an invaluable resource for me.

    David

  4. You can also get other objects (Tables/Views/Functions) when with the following (should work on sql2000 too):

    select
    o.name as tableName,
    c.name as ColumnName,
    o.type as ObjectType,
    u.name as SchemaName
    from syscolumns c
    inner join sysobjects o
    on o.id=c.id
    inner join sysusers u
    on u.uid= o.uid
    where
    c.name like ‘%EmployeeID%’

  5. Hello,

    I need to find the database the table and the column belong to in addition to the schema. Can someone pl. tell me how can I get this information?

    Thank you,
    Mahi

  6. Hi mahi,

    Chekout the column “TABLE:CATALOG” in the information_schema.tables view.

    SELECT * from information_schema.tables

    Hope This Helps
    /Glenn

  7. how to find the available databse in server and how get tables and its details for each databse? Please anybody have any idea???

    Thanks in advance
    Mohan.V

  8. select * from sysobjects where id in(select id from syscolumns where name like’empid%’)

    The above query will return all columns starting with empid.

    Thanks,
    Pushpa

  9. Hi Pinal,

    Is there a way to query an entire server to find a column from all the tables of all databases?
    am creating an application where users will be searching for columns in all databases of our server. the query should return column name,data type,database name,table name.
    any suggestions will be greatly appreciated..

    Thanks,
    Praveen

  10. I want to update the same column from multiple tables in database in sql server 2005.Please help in case of following 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 ‘%EmployeeID%’
    ORDER BY schema_name, table_name;

    I want to update EmployeeID from all tables which returns by above query.

    • You need to use dynamic SQL and table valued variable would be nice also. Consider following (sorry, didn’t have time to test this):

      – Create table to hold schema and table names
      DECLARE @t TABLE(Row int identity(1, 1), SchemaName nvarchar(max), TableName nvarchar(max))

      – Get schema and table names from metadata
      INSERT INTO @t
      SELECT SCHEMA_NAME(schema_id), t.name
      FROM sys.tables AS t
      JOIN sys.columns c ON c.object_id = t.object_id
      WHERE c.name LIKE ‘%EmployeeID%’

      – These are used in iterating through the table
      DECLARE @max int, @i int
      SELECT @i = 1, @max = MAX(Row) IN @t

      – These are used in dynamic SQL
      DECLARE @sql nvarchar(max)
      DECLARE @params nvarchar(max)

      – @newValue is the value entered into EmployeeID
      SET @params = N’@newValue int’

      – Loop de loop
      WHILE @i <= @max
      BEGIN
      – Construct dynamic SQL query
      SET @sql = N'UPDATE [' +
      (SELECT SchemaName FROM @t WHERE Row = @i) +
      N'].[' +
      (SELECT TableName FROM @t WHERE Row = @i) +
      N'] SET EmployeeId = @newValue'

      – Execute query
      EXEC sp_executesql @sql, @params, @newValue

      – Increment iterator
      SET @i = @i + 1
      END

  11. How would you drill down to get the get the column’s value?

    For instance, if I wanted to search all of the columns in a DB looking for the term ‘%Smith%’, how do I reference the columns?

    thanks!!!

  12. Hi Pinal,

    I am using database DB1 and want to find all columns of a table of another database DB2.
    I am trying to do this by Stored procedure but not getting any solutions. Kindly Help me out.

    Thanks,
    Mukesh

  13. Getting an error
    Help me please..
    SQLSTATE = S0002
    [Sybase] [ODBC Driver][Adaptive Server Anywhere]
    Table ‘tables’ not found
    Continue ?

    Thanks

  14. Hiii,,,

    I found the solution for my above said issue.

    For Sybase ASA

    SELECT table_name
    FROM sys.systable, sys.syscolumn
    WHERE sys.systable.table_id = sys.syscolumn.table_id AND sys.syscolumn.column_name = ‘COLUMN_NAME’
    ORDER BY table_name ASC;

    Thanks

  15. Hi,

    I want to find all the tables in the adventureworks where the column employee id having the value ’123′ exists…

    basically i want all the tables in a database where a particular column with a particular value exist.

  16. all of these queries throw following error for me:

    Category Timestamp Duration Message Line Position
    Error 10/25/2010 1:03:34 PM 0:00:00.000 SQL Server Database Error: Incorrect syntax near ‘‘’. 58 0

    line 58 is : where c.name like ‘%EmployeeID%’

    not sure what’s wrong here :S …. can anyone help???

    Thanks!

  17. ok, i got this query working but it only lists column names from the dbo schema. i know i have at least one more schema in the database but its not showing any of the fields/tables from that schema.

    select o.name as tableName, c.name as ColumnName, o.type as ObjectType, u.name as SchemaName
    from syscolumns c inner join sysobjects o on o.id=c.id
    inner join sysusers u on u.uid= o.uid

    any ideas?

    Thanks!

  18. This DOES NOT WORK.

    “FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID”

    Those 2 object_id values have no relationship to one another…

      • Very sorry. I was getting confused somehow by trying to query all foreign key constraints along with their table/column names. Here’s the query in case somebody else stumbles across this:

        select
        object_name(fk.constraint_object_id) ‘ConstraintName’,
        t.name ‘TableName’,
        c.name ‘ColumnName’
        from sys.foreign_key_columns fk
        inner join sys.tables t on fk.parent_object_id = t.object_id
        inner join sys.columns c on t.object_id = c.object_id
        and fk.parent_column_id = c.column_id
        order by ConstraintName

  19. hi,

    i have multiple databases with the names:

    TOY_1 (contains approx 120 tables)
    TOY_2 (contains approx 150 tables)
    TOY_3 (contains approx 100 tables)

    ….
    on SQL srever 2005.

    My question is: Is there a way that i can search by column name across all these databases. For eg:- if there is a column called purchase_id in table XXX of DB TOY_2, but without being aware that in which DB does this coulmn lie, can i fire a query which will search all the tables in all the DBs ( TOY_1, TOY_2,…..) and bring me back the details as to in which Db’s, which table does the column lie?

    I know how to search column name when searching a single database, but i don’t know how to search across all the databases?

    thanks
    aseem

  20. I have seen SEVERAL of your examples over the years and it is an understatement to say THANK YOU VERY MUCH. You have been very helpful and please keep up the good work.

  21. there are > 200 DB in one instance. I want to find out with one query, dbname,schemaname,tablename,column names, column type. Could not which system tables have the relationships

    Your help is appreciated,(my mail id: emailid removed)

    Thanks

  22. Hi,

    Need help to fetch records. I have a table product having 2 field product_id and features. I have 5 following row

    1 Camera
    1 Bluetooth
    1 MP3
    2 Camera
    3 Camera
    3 MP3

    Now I just want to fetch thoes products, having Camera,Bluetooth and MP3

    Please suggest me to build a query

    Thanks
    Sheetal

  23. Hi Madhivanan,

    Thanks very much for your quick response. Best part is I have tried the query as you suggested and its working. Really thanks for your help.

    Sheetal

  24. hi ,
    for eg.
    create view abc as
    SELECT
    a.pen,
    b.book
    from account a, books b

    now i want to know the name of all those views who have books in their FROM clause. Plz suggest me to build the query.
    Thanks in Advance

  25. Hello my dear friends

    I have simple code that is to get a data from couple of tables. But it showing the Result multiple times.Can any body help me.
    THIS IS THE CODE.
    ———————————————————-
    SELECT H1.EMP_CODE,H1.NAME1A+’ ‘+H1.NAME2A+’ ‘+H1.NAME3A ‘NAME’
    ,H2.DESCA ‘JOB’
    ,H3.DESCA ‘COUNTRY’
    FROM H_EMP H1 INNER JOIN H_D_JOB H2
    ON H1.JOB_CODE=H2.CODE
    INNER JOIN H_D_NATIONALITY H3
    ON H1.NAT_CODE=H3.CODE
    WHERE EMP_CODE=7515
    ——————————————————
    OUT PUT
    ——————————————————–
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    7515 شيخ فياز باشا مبرمج هندي
    I’m really Thanks alot to help me.

  26. I have used TM Field Finder for a while and it’s really helpfull when you need to search in SQL Server database for particular text.

  27. CREATE VIEW [Sales].[vSalesPerson]
    AS
    SELECT
    s.[SalesPersonID]
    ,c.[Title]
    ,c.[FirstName]
    ,c.[MiddleName]
    ,c.[LastName]
    ,c.[Suffix]
    ,[JobTitle] = e.[Title]
    ,c.[Phone]
    ,c.[EmailAddress]
    ,c.[EmailPromotion]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,[TerritoryName] = st.[Name]
    ,[TerritoryGroup] = st.[Group]
    ,s.[SalesQuota]
    ,s.[SalesYTD]
    ,s.[SalesLastYear]
    FROM [Sales].[SalesPerson] s
    INNER JOIN [HumanResources].[Employee] e
    ON e.[EmployeeID] = s.[SalesPersonID]
    LEFT OUTER JOIN [Sales].[SalesTerritory] st
    ON st.[TerritoryID] = s.[TerritoryID]
    INNER JOIN [Person].[Contact] c
    ON c.[ContactID] = e.[ContactID]
    INNER JOIN [HumanResources].[EmployeeAddress] ea
    ON e.[EmployeeID] = ea.[EmployeeID]
    INNER JOIN [Person].[Address] a
    ON ea.[AddressID] = a.[AddressID]
    INNER JOIN [Person].[StateProvince] sp
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode];

    Query is : How could we say that the above VIEW follows which type of JOIN by looking at resultset or View ?

  28. Is there any query to find all the references of a particular column in a table? In other way how to find where and all that particular column (not the name, actual column refernce) is used? (ex: sps, user function, FK , views etc)

  29. if you have a ntext column is does not work….
    Example :

    if i want to search for any columns that contain value:%SKUS% it doesn’t show anything…

  30. I’m not a big commenter, but I just wanted to say thanks. I end up finding answers on your blog really often, and I really should take the time to say thank you.

    Soo…thanks for being so concise, and for taking the time to share your knowledge, it is much appreciated.

  31. Hi Guys,

    I often come across the situation where i need to find the column name having particular value of unknown table in a Sql database. For example, I know the value of a column like ‘Test’ and i need to find the Column name and table name in the particular data base. Please share if i can query like that.

    Thanks and Regards,
    Parthiban Sekar

  32. I just wanted to finally say thanks – every time I google a tsql question, whenever your face appears, I follow the link knowing I’ll get good, solid, reliable advice. You sir, give excellent nuggets and are quite an asset to the internet at large. Thank you.

  33. Pinal Sir, the 2nd best part of your blog posts is the large no. of comments, through which one can learn different ideas.
    Thanks for providing us such a useful blog !!!

  34. This works great and I just tried it with date and the only thing I can think of is data type…such as date datetime or smalldatetime
    how would I add a column for the format of the date ?

  35. here is the result

    SELECT Table_Schema, Table_Name, Column_Name, Data_Type
    FROM information_schema.columns
    WHERE table_name in ( select name from sys.objects
    where type = ‘U’ )
    and column_name like ‘%EmployeeID%’
    order by table_schema, table_name

  36. Hi,

    Can someone help me with a script that looks at how many tables in oracle schema that have column name like ‘EmployeeID’?

    Actual I need a very same script that works like the one below but in Oracle aqua, not in ms server.

    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 ‘%EmployeeID%’
    ORDER BY schema_name, table_name;

    Thanks in advance

  37. Hi Dave,

    I have a question.
    Is it possible for you to get data for particulat value.
    For Eg:
    I have a table’ senario1_SRC ‘with 3 columns. col1, col2, col3.

    From the below query i will get ouput as ‘col1′

    select top 1 column_name from information_schema.columns where table_name=’senario1_SRC’

    Next I need the data from above resulted columns.

    Like below
    SELECT
    (select top 1 column_name from information_schema.columns where table_name=’senario1_SRC’)

    FROM senario1_SRC

    If I fire the above query I should get data of the column.

    But I am getting col1 as result

    Can you please help me on above issue.

  38. Brilliant thank you, I used this to find the junction table for two columns in a very large database I was not familiar with by changing the WHERE clause to:

    WHERE c.name LIKE ‘%FIRSTCOLUMN%’
    OR c.name LIKE ‘%SECONDCOLUMN%’

  39. Hello, I am having problems. I need to bring up two different data. Like for example Title and all the pages less then 250 and only those two how can I do it. The table is called books.

  40. I have several tables in a database, which all start with the name Products and I would like to be able to do a search for all ‘part numbers’, and all ‘descriptions’ (which are both columns contained in all of the databases) and then display all of the information in a single table or view.

  41. Hello sir,

    If there are 50 columns in a table and i want display only 48 columns , how it can be done without writing the 48 columns in select query. Is there any way to find this? Please reply me. [email removed]

  42. Pingback: SQL SERVER – Validating Unique Columnname Across Whole Database « SQL Server Journey with SQL Authority

    • sir how to find the list of tables on which insert statement is fired means on which data is inserted date wise
      ????

  43. Can i write an sql to look for a particular account number in all the tables.?
    If all the account number fields in all the tables in the Db are called Account_Num and i want a list of tables that have the Account_Num = ’45′.

  44. Sebas, I don’t know if you have a solution or not, but I was doing this exact thing a few minutes ago. This isn’t the best, but it is quick and easy. In my query below, you’ll need to update the @Column value and @Value values. Run the query into the “Results as Text” window. Copy all of the IF EXISTS lines to a new query window and run it. Enjoy! John

    DECLARE @Column VARCHAR(30) = ‘BACHNUMB’
    DECLARE @Value VARCHAR(30) = ”’12-09-20 B”’ — Note the extra quotes (‘) because I’m looking for character vaue. ’10′ will work if you are looking for a number

    SELECT ‘IF EXISTS(SELECT * FROM ‘ + sys.tables.name + ‘ WHERE ‘ + sys.columns.name + ‘ = ‘ + @Value + ‘) BEGIN PRINT ”’ + sys.tables.name + ”’; SELECT * FROM ‘ +
    sys.tables.name + ‘ WHERE ‘ + sys.columns.name + ‘ = ‘ + @Value + ‘ END’ AS table_name
    FROM sys.tables
    JOIN sys.columns ON sys.tables.OBJECT_ID = sys.columns.OBJECT_ID
    WHERE sys.columns.name LIKE @Column
    ORDER BY table_name;

  45. This i belive consumers less query execution time..
    select TABLE_NAME as ‘table name’, COLUMN_NAME as ‘column name’
    FROM INFORMATION_SCHEMA.COLUMNS
    order by table_name

  46. dear all

    is it possible to get all columns from a table except some specific one like::

    select * from table where column_name column_name ;

    i need to retrieve 8 columns out of 10 from a table

  47. Thanks for this, very helpful when working your way into an existing system that is not designed properly.

  48. I have table name as @Table_Name
    I have column value as @Value but dont have Column name ( but that exist at 1st position )

    how can I compare that table column name value ?

    I want something like

    SELECT * FROM @Table_Name
    WHERE Table.Column[1].Value = @Value

    How can I do that ……

  49. I have table name as @Table_Name
    How can I get 1st Column Name from MyTable(@Table_Name) without knowing Column name ..??

  50. DECLARE @column_name varchar(20)
    @column_name = SELECT top 1 column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = ‘bb_match’

    select * from bb_match
    where @column_name = 6

    this is not working ..where is mistake ???

  51. This is showing only column name not showing data ??

    DECLARE @column_name varchar(20)
    set @column_name = (SELECT top 1 column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = ‘bb_match’)
    select * from bb_match
    where @column_name = ’8′

    how can show data or this record ???

  52. how to find all the tables in a database which contains a column referenced from a particular table in the databse?

  53. Hi Pinal – I’m looking for a query which will return me database name, table name, column name for a search value. e.g. I want to search for a value = “test rating” across all tables in the database.

    I have some queries, but they return values for only user created tables and not system tables or tables created by an asp.net web application for instance. Any help is appreciated!

  54. Thank you.
    The basic script has saved me from having to manually trawl through a 2GB database with over 500 tables.

  55. You rock Pinal. I always check your blogs first when I have SQL Server questions as your answers are always easy to understand and straight to the point.

  56. Pinal, Thanks for all the articles you produce. Like Paul above, I too look here first for help. Using your original script, I developed this to traverse all databases in an instance. I hope it helps someone out.

    /************** Drop table if it exists **************************/
    USE [MASTER]
    If OBJECT_ID(‘PIITable’,’U’) IS NOT NULL
    DROP TABLE PIITABLE

    /************** Create temp table in master **************************/

    Create table PIITable(ID Int Identity (1,1),
    Database_Name NVARCHAR(100),Table_Name NVARCHAR(100),SCHEMA_NAME NVARCHAR(20),
    COLUMN_NAME NVARCHAR(100),Data_Type NVARCHAR(100))

    /************** Create table variable to store database names **************************/
    Declare @Databases table
    (id int identity (1,1),DatabaseName nvarchar(100))

    /************** Insert database names into table variable **************************/
    Insert into @Databases

    SELECT name [Database Name]
    FROM [model].[sys].[databases]
    where (database_id > 4 and Name Not In(‘Distribution’))
    order by name

    –select * from @Databases –Select all databases from table variable (For testing only)

    /***************************** Set up variables for loop **********************/

    Declare @row int
    Declare @rows int
    Declare @database_name Nvarchar(MAX)
    Declare @Execdatabase Nvarchar(MAX)

    /***************************** Set up loop execution *****************************/
    Select @rows = (Select MAX(id) from @Databases)
    Set @row = 1

    While @row <= @rows
    Begin
    Set @database_name = (Select DatabaseName from @Databases where id = @row)

    Set @Execdatabase =
    'Use '+ ' '+ '['+ @database_name +']'+
    'Select ('''+ @database_name +''')as Database_Name, t.name AS table_name ,
    SCHEMA_NAME( t.schema_id
    )AS schema_name , c.name AS column_name,sys.types.name Data_Type INTO ##PrePIITable
    FROM sys.tables AS t INNER JOIN
    sys.columns AS c ON t.object_id = c.object_id INNER JOIN
    sys.types ON c.system_type_id = sys.types.system_type_id

    WHERE c.name IN( ''SSN'', ''FirstName'' , ''LastName'' , ''First_Name'' ,''Last_Name'' , ''MiddleName''
    , ''Name_First'' , ''Name_Last''
    )
    OR c.name LIKE ''%Form%'' OR c.name LIKE ''%addr%''

    GROUP BY t.name ,
    SCHEMA_NAME( t.schema_id
    ) , c.name,sys.types.name
    ORDER BY schema_name , table_name; '

    /***************** Execute above script **************************/
    exec (@Execdatabase)
    /********* Insert results into temporary Table (Master.dbo.PIITable) **************/
    INSERT INTO Master.dbo.PIITable
    Select * from ##PrePIITable

    /***************** Drop temp table for next itteration of loop **************************/
    If OBJECT_ID('tempdb..##PrePIITable','U') IS NOT NULL
    DROP TABLE ##PrePIITable

    Set @row = @row + 1
    End
    /***************** Select entire result set from PIITable **************************/

    Select Serverproperty('Servername') as 'Server',
    Isnull(Serverproperty('Instancename'),'Default')'Instance_Name',
    database_Name,Table_Name,Schema_Name,Column_Name,Data_Type

    from master.dbo.PIITable
    Where Data_Type ‘uniqueidentifier’
    Group By database_Name,Table_Name,Schema_Name,Column_Name,Data_Type
    order by Database_Name

    /***************** Select Database/table result set from PIITable *******************/

    Select Serverproperty(‘Servername’) as ‘Server’,
    Isnull(Serverproperty(‘Instancename’),’Default’)’Instance_Name’,
    database_Name,Table_Name,Schema_Name,Data_Type

    from master.dbo.PIITable
    Where Data_Type ‘uniqueidentifier’
    Group by database_name,Table_Name,Schema_Name,Data_Type
    order by Database_Name

    /********************** Clean up temp table in master ************/
    USE [MASTER]
    If OBJECT_ID(‘PIITable’,’U’) IS NOT NULL
    DROP TABLE PIITABLE

    • Also, when searching for ethnic this is the query I used:

      USE [INSERT DATABASE NAME]
      GO
      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 ‘%ethnic%’
      ORDER BY schema_name, table_name;

  57. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

  58. Hi, I would like to know how search for a column name with instances in several tables within a database and return the column value for each.

  59. If I just know a Database Name alone and wanted to know all maximum used columnnames across tables, is there any query to find that? this helps in understanding any Database for a first person logging into that DB

  60. i want to find a column in a table when that column is updated by update query
    thanks in advance…….

  61. I am so annoyed with this error

    SELECT `groups`.`name`, `groups`.`title` FROM `v242_config_groups` `groups` JOIN `v242_config` `config` ON `groups`.`name` = `config`.`group_name` GROUP BY `groups`.`name` ORDER BY `groups`.`order` ASC

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s