Find all tables containing column with specified name - MS SQL Server

Question

Is it possible to query for table names which contain columns being  LIKE   myName

User · Answer

Search Tables  SELECT      c name  AS  ColumnName               t name AS  TableName  FROM        sys columns c JOIN        sys tables  t   ON c object id   t object id WHERE       c name LIKE   MyName   ORDER BY    TableName              ColumnName   Search Tables and Views  SELECT      COLUMN NAME AS  ColumnName               TABLE NAME AS   TableName  FROM        INFORMATION SCHEMA COLUMNS WHERE       COLUMN NAME LIKE   MyName   ORDER BY    TableName              ColumnName

User · Answer

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   Label   ORDER BY schema name  table name

User · Answer

This should work   SELECT name  FROM sysobjects  WHERE id IN   SELECT id                FROM syscolumns                WHERE name like   column name

User · Answer

SQL Server   SELECT Table Name  Column Name  FROM INFORMATION SCHEMA COLUMNS WHERE TABLE CATALOG    YOUR DATABASE  AND COLUMN NAME LIKE   YOUR COLUMN     Oracle   SELECT owner  table name  column name  FROM all tab columns  WHERE column name LIKE   YOUR COLUMN NAME   AND OWNER IN   YOUR SCHEMA NAME       SIMPLE AS THAT    SQL  PL SQL  I use it ALL the time to find ALL instances of a column name in a given database  schema

User · Answer

You can find it from INFORMATION SCHEMA COLUMNS by column name filter   Select DISTINCT TABLE NAME as TableName COLUMN NAME as ColumnName      From INFORMATION SCHEMA COLUMNS Where column name like   myname

User · Answer

i have just tried it and this works perfectly  USE YourDatabseName 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   YourColumnName   ORDER BY schema name  table name    Only change YourDatbaseName to your database and YourcolumnName to your column name that you are looking for the rest keep it as it is   Hope this has helped

User · Answer

I don t know why so many of you suggesting Joining with sys table with sys columns you can simply use below code   Select object name object id  as TableName   from  SYS columns where name LIKE   MyName    or   If you want schema name as well   Select   from  INFORMATION SCHEMA COLUMNS where COLUMN NAME LIKE   MyName

User · Answer

We can use sp columns for the purpose   sp columns  table name   null  null    column name

User · Answer

Here is the answer to your question  SELECT c name AS ColumnName  t name AS TableName FROM sys columns c     JOIN sys tables t ON c object id   t object id WHERE c name LIKE   myName

User · Answer

If you simply want the table name you can run   select object name object id  from sys columns where name like   received at     If you want the Schema Name as well  which in a lot of cases you will  as you ll have a lot of different schemas  and unless you can remember every table in the database and where it belongs this can be useful  run   select OBJECT SCHEMA NAME object id  object name object id  from sys columns where name like   received at     and finally if you want it in a nicer format  although this is where the code  In my opinion  is getting too complicated for easy writing    select concat OBJECT SCHEMA NAME object id      object name object id   from sys columns where name like   received at     note you can also create a function based on what I have   CREATE PROCEDURE usp tablecheck --Scan through all tables to identify all tables with columns that have the provided string --Stephen B  name nvarchar 200  AS SELECT CONCAT OBJECT SCHEMA NAME object id      object name object id   AS  Table Name   name AS  Column  FROM sys columns WHERE name LIKE CONCAT      name      ORDER BY  Table Name  ASC   Column  ASC GO   It is worth noting that the concat feature was added in 2012  For 2008r2 and earlier use   to concatenate strings   I ve re-formatted the proc a bit since I posted this  It s a bit more advanced now but looks a lot messier  but it s in a proc so you ll never see it  and it s formatted better   This version allows you to have it in an administrative database and then search through any database  Change the decleration of  db from  master  to whichever you want the default database to be  NOTE  using the CONCAT   function will only work with 2012  unless you change the string concatenation to use the   operators    CREATE PROCEDURE  dbo   usp tablecheck      --Scan through all tables to identify all tables in the specified database with columns that have the provided string     --Stephen B      name nvarchar 200        db nvarchar 200     master  AS     DECLARE  sql nvarchar 4000    CONCAT           SELECT concat OBJECT SCHEMA NAME col object id DB ID      db             object name col object id DB ID      db        AS  Table Name               col name AS  Column           FROM    db   sys columns col         LEFT JOIN    db   sys objects ob              ON ob object id   col object id         WHERE              col name LIKE CONCAT            name                         AND ob type    U           ORDER BY  Table Name  ASC               Column  ASC       EXECUTE   sql  GO

User · Answer

Hopefully this isn t a duplicate answer  but what I like to do is generate a sql statement within a sql statement that will allow me to search for the values I am looking for  not just the tables with those field names   as it s usually necessary for me to then delete any info related to the id of the column name I am looking for      SELECT   Select   from     t name     where     c name       148  AS SQLToRun   FROM sys columns c  c name as ColName  t name as TableName   JOIN sys tables t       ON c object id   t object id   WHERE c name LIKE   ProjectID     Then I can copy and paste run my 1st column  SQLToRun     then I replace the  Select   from   with  Delete from   and it allows me to delete any references to that given ID  Write these results to file so you have them just in case   NOTE     Make sure you eliminate any bakup tables prior to running your your delete statement        SELECT   Delete from     t name     where     c name       148  AS SQLToRun   FROM sys columns c  c name as ColName  t name as TableName   JOIN sys tables t       ON c object id   t object id   WHERE c name LIKE   ProjectID

User · Answer

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     It is from Pinal Sir Blog

User · Answer

In addition  you can find column name with specified schema also   SELECT  DBName  as DBName  COLUMN NAME  TABLE NAME  TABLE SCHEMA     FROM  DBName INFORMATION SCHEMA COLUMNS       WHERE COLUMN NAME LIKE   YourColumnName   and TABLE SCHEMA IN   YourSchemaName    You can also find same column on multiple database  SELECT  DBName1  as DB  COLUMN NAME  TABLE NAME  TABLE SCHEMA FROM DBName1 INFORMATION SCHEMA COLUMNS     WHERE COLUMN NAME LIKE   YourColumnName     UNION  SELECT  DBName2  as DB   COLUMN NAME  TABLE NAME  TABLE SCHEMA FROM DBName2 INFORMATION SCHEMA COLUMNS     WHERE COLUMN NAME LIKE   YourColumnName

User · Answer

SELECT COLUMN NAME  TABLE NAME   FROM INFORMATION SCHEMA COLUMNS      WHERE COLUMN NAME LIKE   myName

User · Answer

We can also use the following syntax -  select   from INFORMATION SCHEMA COLUMNS  where COLUMN NAME like   clientid    order by TABLE NAME

User · Answer

DECLARE  columnName as varchar 100  SET  columnName    ColumnName   SELECT t name AS Table  c name AS Column  ty name AS Type  c max length AS Length FROM sys tables AS t INNER JOIN sys columns c ON t OBJECT ID   c OBJECT ID INNER JOIN sys types ty ON c system type id   ty system type id WHERE c name LIKE  columnName ORDER BY t name  c name

User · Answer

To get full information  column name  table name as well as schema of the table    SELECT COLUMN NAME  TABLE NAME  TABLE SCHEMA FROM INFORMATION SCHEMA COLUMNS  WHERE COLUMN NAME LIKE   col Name

User · Answer

I used this for the same purpose and it worked     select   from INFORMATION SCHEMA COLUMNS   where TABLE CATALOG   theDatabase    and COLUMN NAME like  theCol

User · Answer

select           s  name              Schema           t  name              Table           c  name              Column           d  name              Data Type           c  max length        Length           d  max length        Max Length           d  precision         Precision           c  is identity       Is Id           c  is nullable       Is Nullable           c  is computed       Is Computed           d  is user defined   Is UserDefined           t  modify date       Date Modified           t  create date       Date created  from        sys schemas s inner join  sys tables  t on s schema id   t schema id inner join  sys columns c on t object id   c object id inner join  sys types   d on c user type id   d user type id where c name like   ColumnName     This here will give you a little extra information about the schema  tables and columns that you may or may not choose to use extra conditions in your where clause to filter on  For example  if you only wanted to see the fields which must have values add  and c is nullable   0   You could add other conditionals  I also added the columns in the select clause in this vertical manner so it was easy to reorder  remove  rename  or add others based on your needs  Alternately you could search for just tables by using T Name  Its very customisable   Enjoy

User · Answer

In MS SQL  you can write the below line to check the column names of a particular table  sp help your tablename  Or  you can first select your table name in the query windows  highlight the schema and table name  and then press key combination below  Alt   F1

User · Answer

If you   re more into third party tools there a lot of options there such as    ApexSQL Search SSMS Toolpack Red Gate tools     These come in very handy if your database contains encrypted objects  views  procedures  functions  because you can   t easily search for these using system tables

User · Answer

Create table  yourcolumndetails  DBaseName varchar 100    TableSchema varchar 50    TableName varchar 100   ColumnName varchar 100    DataType varchar 100    CharMaxLength varchar 100    EXEC sp MSForEachDB  command1  USE          INSERT INTO  yourcolumndetails SELECT     Table Catalog      Table Schema      Table Name      Column Name      Data Type      Character Maximum Length     FROM INFORMATION SCHEMA COLUMNS     WHERE COLUMN NAME like   origin     select   from  yourcolumndetails Drop table  yourcolumndetails

User · Answer

In MS SQL Server Database  use this query to get the tables and respective column names that contains the input text  SELECT t name AS tableName  c name AS columnName  FROM sys tables as t  INNER JOIN sys columns AS c ON t object id c object id  WHERE c name LIKE    lt your search string gt

User · Answer

Just to improve on the answers above i have included Views as well and Concatenated the Schema and Table View together making the Results more apparent   DECLARE  COLUMNNAME AS VARCHAR 100    SET  COLUMNNAME     Absence     SELECT CASE            WHEN  T   NAME  IS NULL            THEN  View             WHEN  T   NAME                  THEN  View             ELSE  Table         END AS  TYPE          S   NAME                 CASE                                                           WHEN  T   NAME  IS NULL                                                           THEN  V   NAME                                                            WHEN  T   NAME                                                                 THEN  V   NAME                                                            ELSE  T   NAME                                                        END       AS  TABLE    C   NAME  AS  COLUMN  FROM  SYS   SCHEMAS  AS  S  LEFT JOIN  SYS   TABLES  AS  T  ON  S  SCHEMA ID    T  SCHEMA ID                             LEFT JOIN  SYS   VIEWS  AS  V  ON  S  SCHEMA ID    V  SCHEMA ID                             INNER JOIN  SYS   COLUMNS  AS  C  ON  T  OBJECT ID    C  OBJECT ID                                                                  OR                                                                   V  OBJECT ID    C  OBJECT ID                             INNER JOIN  SYS   TYPES  AS  TY  ON  C   SYSTEM TYPE ID     TY   SYSTEM TYPE ID  WHERE  C   NAME  LIKE  COLUMNNAME GROUP BY        S   NAME                 CASE                                              WHEN  T   NAME  IS NULL                                              THEN  V   NAME                                               WHEN  T   NAME                                                    THEN  V   NAME                                               ELSE  T   NAME                                           END         T   NAME    C   NAME    S   NAME  ORDER BY        S   NAME                 CASE                                              WHEN  T   NAME  IS NULL                                              THEN  V   NAME                                               WHEN  T   NAME                                                    THEN  V   NAME                                               ELSE  T   NAME                                           END        CASE                                                         WHEN  T   NAME  IS NULL                                                         THEN  View                                                          WHEN  T   NAME                                                               THEN  View                                                          ELSE  Table                                                      END   T   NAME    C   NAME

User · Answer

Here s a working solution for a Sybase database  select    t table name     c column name  from    systab as t key join systabcol as c  where     c column name    MyColumnName

User · Answer

Like oracle you can find tables and columns with this   select table name  column name from user tab columns  where column name  like   myname

User · Answer

SQL query to show all tables that have specified column name    SELECT SCHEMA NAME schema id          t name AS  Table Name    FROM sys tables t  INNER JOIN sys columns c ON c object id   t object id  WHERE c name like   ColumnName    ORDER BY  Table Name

User · Answer

You can try this query   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   myName

User · Answer

SELECT   TABLE NAME             INFORMATION SCHEMA  COLUMNS COLUMN NAME FROM    INFORMATION SCHEMA COLUMNS WHERE   INFORMATION SCHEMA COLUMNS COLUMN NAME LIKE   NAME

User · Answer

I wanted something for tables and views that didn t make my eyes bleed   Query  SELECT     t TABLE TYPE AS  Type       c TABLE NAME AS  Object       c COLUMN NAME AS  Column  FROM     INFORMATION SCHEMA COLUMNS AS c     LEFT JOIN INFORMATION SCHEMA TABLES AS t ON         t TABLE CATALOG   c TABLE CATALOG AND          t TABLE SCHEMA   c TABLE SCHEMA AND         t TABLE NAME   c TABLE NAME WHERE     c COLUMN NAME LIKE   myName   ORDER BY      Type        Object        Column    Result  Type        Object   Column ---------------------------- BASE TABLE  Table1   myName1 BASE TABLE  Table2   myName2 VIEW        View1    myName1 VIEW        View2    myName2

User · Answer

Following query will give you the exact table names of the database having field name like   myName    SELECT distinct TABLE NAME    FROM INFORMATION SCHEMA COLUMNS      WHERE COLUMN NAME LIKE   myName

[sql] Find all tables containing column with specified name - MS SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to system-tables