Is it possible to query for table names which contain columns being
LIKE '%myName%'
Is it possible to query for table names which contain columns being
LIKE '%myName%'
Search Tables:
SELECT c.name AS 'ColumnName'
,(SCHEMA_NAME(t.schema_id) + '.' + 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;
I don't know why many of you suggest joining with sys.table with sys.columns
.
You can use the 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%'
To find all tables containing a column with a specified name in MS SQL Server, you can query the system catalog views. Specifically, you can query the sys.tables and sys.columns views. Here's an example query:
SELECT t.name AS table_name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'column_name';
Replace column_name
with the name of the column you want to search for. This query joins the sys.tables and sys.columns views based on the object_id column and filters the result to retrieve the table names where the column name matches the specified value.
In dbForge Studio for SQL Server, you can use the SQL editor to execute the query mentioned above. It offers features like syntax highlighting, code completion, and result viewing, making it easier to write and execute SQL queries efficiently.
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%'
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
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];
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
In addition, you can find column names 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 the same column on multiple databases.
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%'
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
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
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
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');
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.
I used this for the same purpose and it worked: