Sql Server Search for a Column by Name

SQL Server search for a column by name

SELECT OBJECT_NAME(object_id) FROM sys.columns WHERE name = 'foo'

This includes views though but can be further filtered . It may be useful though.

More generally...

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'foo'

sys.columns

How to find specific column in SQL Server database?

You can query the database's information_schema.columns table which holds the schema structure of all columns defined in your database.

Using this query:

select * from information_schema.columns where column_name = 'ProductNumber'

The result would give you the columns:

TABLE_NAME, TABLE_CATALOG, DATA_TYPE and more properties for this database column.

Find all table names with column name?

Please try the below query. Use sys.columns to get the details :-

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyCol%';

How to search a column name in all tables in a database in SQL Server 2012?

Try the below

Select distinct object_name(object_id), name from sys.columns where name like '%tax%'

or

select table_name, Column_name from Information_Schema.Columns where Column_Name like '%Tax%'

Find all stored procedures that reference a specific column in some table

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

find sql table name with a particular column

In SQL Server, you can query sys.columns.

Something like:

 SELECT
     t.name
 FROM
     sys.columns c
        inner join
     sys.tables t
        on
           c.object_id = t.object_id
 WHERE
     c.name = 'NameID'

You might want an additional lookup to resolve the schema name, if you have tables in multiple schemas.

How to find all tables and datasets/databases which have a specific column name in big query

I found the solution is to replace the dataset name with region-us instead.

The below works for looking up across tables and datasets

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.TABLES
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

The below works for views:

SELECT
    ddl 
FROM
    `project-name`.`region-us`.INFORMATION_SCHEMA.VIEWS
WHERE 
    table_name like '%sender%' 
    AND ddl LIKE '%sender_country%' 

My table contains a column name title. I want to sql search filter male and female from title column

You need to include male and explicit exclude female from the text.

If you want the second line you need somewhat more complicated query

CREATE TABLE laws (title varchar(50))

INSERT INTO laws VALUEs ('Only for male partitipents'),('male and female partitipents'),('No more female partitipents in this law')

SELECT * FROM `laws` WHERE title LIKE '%male%' AND title NOT LIKE  '%female%'

| title                      |
| :------------------------- |
| Only for male partitipents |

db<>fiddle here

How to find column names for all tables in all databases in SQL Server

Try this:

select 
    o.name,c.name 
    from sys.columns            c
        inner join sys.objects  o on c.object_id=o.object_id
    order by o.name,c.column_id

With resulting column names this would be:

select 
     o.name as [Table], c.name as [Column]
     from sys.columns            c
         inner join sys.objects  o on c.object_id=o.object_id
     --where c.name = 'column you want to find'
     order by o.name,c.name

Or for more detail:

SELECT
    s.name as ColumnName
        ,sh.name+'.'+o.name AS ObjectName
        ,o.type_desc AS ObjectType
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
            WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name
         END AS DataType

        ,CASE
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL'
        END AS Nullable
        ,CASE
             WHEN ic.column_id IS NULL THEN ''
             ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
         END
        +CASE
             WHEN sc.column_id IS NULL THEN ''
             ELSE ' computed('+ISNULL(sc.definition,'')+')'
         END
        +CASE
             WHEN cc.object_id IS NULL THEN ''
             ELSE ' check('+ISNULL(cc.definition,'')+')'
         END
            AS MiscInfo
    FROM sys.columns                           s
        INNER JOIN sys.types                   t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
        INNER JOIN sys.objects                 o ON s.object_id=o.object_id
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
    ORDER BY sh.name+'.'+o.name,s.column_id

EDIT
Here is a basic example to get all columns in all databases:

DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select 
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.column_id
from '+d.name+'.sys.columns            c
    inner join '+d.name+'.sys.objects  o on c.object_id=o.object_id
    INNER JOIN '+d.name+'.sys.schemas  sh on o.schema_id=sh.schema_id
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)

EDIT SQL Server 2000 version

DECLARE @SQL varchar(8000)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select 
'''+d.name+'.''+sh.name+''.''+o.name,c.name,c.colid
from '+d.name+'..syscolumns            c
    inner join sysobjects  o on c.id=o.id
    INNER JOIN sysusers  sh on o.uid=sh.uid
'
FROM master.dbo.sysdatabases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)

EDIT
Based on some comments, here is a version using sp_MSforeachdb:

sp_MSforeachdb 'select 
    ''?'' AS DatabaseName, o.name AS TableName,c.name AS ColumnName
    from sys.columns            c
        inner join ?.sys.objects  o on c.object_id=o.object_id
    --WHERE ''?'' NOT IN (''master'',''msdb'',''tempdb'',''model'')
    order by o.name,c.column_id'

---

Related Topics