Salman Arshad
Reputation: 272006
How to find column names for all tables in all databases in SQL Server
I want to find all column names in all tables in all databases. Is there a query that can do that for me?
Upvotes: 88
Views: 250168
Answers (17)
Angel
Reputation: 2865
Get all columns with table names from ALL DATABASES
With this Python (3.12 tested) function you can get an SQL query to get an union of all the columns from different databases
def sql_all_columns(databases: list[str], collation: str):
"""Prints SQL query to get all columns of all databases"""
db_sql = "SELECT table_catalog COLLATE {cl} AS db, table_schema COLLATE {cl} AS [schema], table_name COLLATE {cl} AS [table], column_name COLLATE {cl} AS [column] FROM [{db}].INFORMATION_SCHEMA.columns"
stmt = []
for db in databases:
stmt.append(db_sql.format(cl=collation, db=db))
all_columns = "\n UNION\n".join(stmt)
sql = f"""
WITH all_columns AS (
{all_columns}
)
SELECT *
FROM all_columns"""
print(sql)
sql_all_columns(
["database1", "database2", "otherdatabase"],
collation="SQL_Latin1_General_CP1_CI_AS",
)
The execution will print the following SQL statement
WITH all_columns AS (
SELECT table_catalog COLLATE SQL_Latin1_General_CP1_CI_AS AS db, table_schema COLLATE SQL_Latin1_General_CP1_CI_AS AS [schema], table_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [table], column_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [column] FROM [database1].INFORMATION_SCHEMA.columns
UNION
SELECT table_catalog COLLATE SQL_Latin1_General_CP1_CI_AS AS db, table_schema COLLATE SQL_Latin1_General_CP1_CI_AS AS [schema], table_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [table], column_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [column] FROM [database2].INFORMATION_SCHEMA.columns
UNION
SELECT table_catalog COLLATE SQL_Latin1_General_CP1_CI_AS AS db, table_schema COLLATE SQL_Latin1_General_CP1_CI_AS AS [schema], table_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [table], column_name COLLATE SQL_Latin1_General_CP1_CI_AS AS [column] FROM [otherdatabase].INFORMATION_SCHEMA.columns
)
SELECT *
FROM all_columns
To get ALL the databases with permission:
SELECT name
FROM sys.sysdatabases
WHERE HAS_DBACCESS(name) = 1
You can now copy and paste the names list into the Python script
Upvotes: 0
Ardalan Shahgholi
Reputation: 12555
user @KM say best Answer.
I Use This :
Declare @Table_Name VarChar(100) ,@Column_Name VarChar(100)
Set @Table_Name = ''
Set @Column_Name = ''
Select
RowNumber = Row_Number() Over( PARTITION BY T.[Name] Order By T.[Name],C.column_id ),
SCHEMA_NAME( T.schema_id ) As SchemaName ,
T.[Name] As Table_Name ,
C.[Name] As Field_Name ,
sysType.name ,
C.max_length , C.is_nullable , C.is_identity , C.scale , C.precision
From Sys.Tables As T
Left Join Sys.Columns As C On ( T.[Object_Id] = C.[Object_Id] )
Left Join sys.types As sysType On ( C.user_type_id = sysType.user_type_id )
Where ( Type = 'U' )
And ( C.Name Like '%' + @Column_Name + '%' )
And ( T.Name Like '%' + @Table_Name + '%' )
Upvotes: 2
TheMah
Reputation: 459
Here, this is my code to search for a column name in all databases in the current instance:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: themah
-- Create date: 2022-10-09
-- Description: Looking for a column name in all databases in current instance, and list the db names
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[FindColumnNameInAllDatabases]
@columnNameToFind NVARCHAR (100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @dbName VARCHAR(100)
DECLARE @queryEachDatabase NVARCHAR(MAX)
DECLARE dbNamesCursor CURSOR FOR
SELECT [Name]
FROM sys.databases AS d
WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
OPEN dbNamesCursor
FETCH NEXT FROM dbNamesCursor INTO @dbName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @queryEachDatabase = N'IF Exists
(SELECT 1 FROM [' + @dbName + '].[INFORMATION_SCHEMA].[Columns] WHERE [Column_Name] Like N''%' + @columnNameToFind + '%'')
BEGIN
PRINT ''' + @dbName + '''
END
'
--PRINT @queryEachDatabase
EXEC(@queryEachDatabase)
FETCH NEXT FROM dbNamesCursor INTO @dbName
END
CLOSE dbNamesCursor
DEALLOCATE dbNamesCursor
END
GO
And a usage example : EXEC dbo.FindColumnNameInAllDb N'firstName'
Or another useful way to print the queries is this :
DECLARE @columnName NVARCHAR(100) = N'firstName'
SELECT
'SELECT * FROM [' + NAME + '].[INFORMATION_SCHEMA].[Columns] WHERE [Column_Name] Like N''%' + @columnName + '%'''
FROM [sys].[databases]
Upvotes: 1
Valderann
Reputation: 835
Some minor improvements
->previous answers weren't showing all results
->possible to filter on column name by setting the column name variable
DECLARE @columnname nvarchar(150)
SET @columnname=''
DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
SELECT
'''+d.name+'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS as name,c.name COLLATE SQL_Latin1_General_CP1_CI_AS as columnname,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
WHERE c.name like ''%'+@columnname+'%'' AND sh.name<>''sys''
'
FROM sys.databases d
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1,3'
--print @SQL
EXEC (@SQL)
Upvotes: 4
Howard Rothenburg
Reputation: 1348
sp_MSForEachDB @command1='USE ?;
SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE --Data_Type = ''int'' --AND COLUMN_NAME like ''%ColumnName%'' And Table_name like ''%TableName%'' order by 1,2,3 '
Upvotes: 0
VikKlein
Reputation: 1
My solution for similar task was:
if object_id('tempdb..##temp_meta') is not null
drop table ##temp_meta
create table ##temp_meta(database_name sysname
, schema_name sysname
, table_name sysname
, column_name sysname
, column_id tinyint
, type_name sysname
, max_length int
, precision tinyint
, scale tinyint
, is_nullable bit
, is_identity bit)
exec sp_MSforeachdb '
if(''?'' not in (''master'', ''msdb'', ''model'', ''tempdb''))
begin
USE [?]
insert into ##temp_meta
SELECT
''?'',
s.[name] AS schema_name,
t.name AS table_name,
c.[name] as column_name,
c.column_id,
ty.name as type_name,
c.max_length,
c.precision,
c.scale,
c.is_nullable,
c.is_identity
FROM sys.columns c
JOIN sys.types ty
ON ty.system_type_id = c.system_type_id
JOIN sys.tables t
ON c.object_id = t.object_id
JOIN sys.schemas s
ON s.schema_id = t.schema_id
end'
Upvotes: 0
Max Wright
Reputation: 41
I used:
EXEC sp_MSforeachdb 'Use ? Select * From INFORMATION_SCHEMA.COLUMNS '
It seems to work for what I needed.
Upvotes: 3
Shweta Yadav
Reputation: 29
SELECT sys.columns.name AS ColumnName, tables.name AS TableName
FROM sys.columns
JOIN sys.tables ON sys.columns.object_id = tables.object_id
Upvotes: 2
KM.
Reputation: 103579
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'
Upvotes: 121
Samuel Nde
Reputation: 2743
I just realized that the following query would give you all column names from the table in your database (SQL SERVER 2017)
SELECT DISTINCT NAME FROM SYSCOLUMNS
ORDER BY Name
OR SIMPLY
SELECT Name FROM SYSCOLUMNS
If you do not care about duplicated names.
Another option is SELECT Column names from INFORMATION_SCHEMA
SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY column_name
It is usually more interesting to have the TableName as well as the ColumnName ant the query below does just that.
SELECT
Object_Name(Id) As TableName,
Name As ColumnName
FROM SysColumns
And the results would look like
TableName ColumnName
0 Table1 column11
1 Table1 Column12
2 Table2 Column21
3 Table2 Column22
4 Table3 Column23
Upvotes: 3
Nandish B
Reputation: 212
try the below query
DECLARE @Query VARCHAR(max)
SELECT @Query = 'USE ? SELECT ''?'' AS DataBaseName,
sys.columns.name AS ColumnName ,
sys.tables.name AS TableName ,
schema_name (sys.tables.schema_Id) AS schemaName
FROM sys.columns
JOIN sys.tables
ON sys.columns.object_id = sys.tables.object_id
WHERE sys.columns.name = ''id'' '
EXEC SP_MSFOREACHDB @Query
gives list of tables containing ID column from all databases.
Upvotes: 2
user5576010
Reputation: 121
Better way for you
sp_MSForEachDB @command1='USE ?;
SELECT
Table_Catalog
,Table_Schema
,Table_Name
,Column_Name
,Data_Type
,Character_Maximum_Length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ''%ColumnNameHere%'''
Upvotes: 11
Hank Freeman
Reputation: 1212
To all: Thanks for all the post and comments some are good, but some are better.
The first big script is good because it is delivers just what is needed. The fastest and most detailed is the one suggestion for selecting from INFORMATION_SCHEMA.COLUMNS..
My need was to find all the errant columns of approximately the same name and Several databases.. Sooo, I made my versions of both (see below) ...Either of these two below script work and deliver the goods in seconds.
The assumption in other posts on this link, is that the first code example can be used successfully with for-each-database, is to me, not desirable. This is because the information is within the specific database and the simple use of the "fedb" doesn't produce the correct results, it simply doesn't give access. SOOO to that is why I use a CURSOR to collect the databases and ignore those that are Off-line, which in this case, a utility script, it is a good use of same.
Bottom Line, I read everyone's post, incorporated all the correction from the posts and made what are two very eloquent scripts from others good works. I listed both below and have also placed the script file on my public folder at OneDrive.com which you can access with this link: http://1drv.ms/1vr8yNX
Enjoy ! Hank Freeman
Senior Level - SQL Server DBA - Data Architect
Try them separately...
---------------------------
--- 1st example (works) ---
---------------------------
Declare
@DBName sysname
,@SQL_String1 nvarchar(4000)
,@SQL_String2 nvarchar(4000)
,@ColumnName nvarchar(200)
--set @ColumnName = 'Course_ID'
-------- Like Trick --------
-- IF you want to add more the @ColumnName so it looks like Course_ID,CourseID
-- then add an additional pairing of +''','''+'NewColumnSearchIDValue'
----------------------------
set @ColumnName = 'Course_ID' +''','''+'CourseID'
--select @ColumnName
-----
Declare @Column_Info table
(
[DatabaseName] nvarchar(128) NULL,
[ColumnName] sysname NULL,
[ObjectName] nvarchar(257) NOT NULL,
[ObjectType] nvarchar(60) NULL,
[DataType] nvarchar(151) NULL,
[Nullable] varchar(8) NOT NULL,
[MiscInfo] nvarchar(MAX) NOT NULL
)
--------------
Begin
set @SQL_String2 = 'SELECT
DB_NAME() as ''DatabaseName'',
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
into ##Temp_Column_Info
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
--------------------------------------------
--- DBA - Hank 12-Feb-2015 added this specific where statement
-- where Upper(s.name) like ''COURSE%''
-- where Upper(s.name) in (''' + @ColumnName + ''')
-- where Upper(s.name) in (''cycle_Code'')
-- ORDER BY sh.name+''.''+o.name,s.column_id
order by 1,2'
--------------------
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
--select * from sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
--select @DBName as '@DBName';
Set @SQL_String1 = 'USE [' + @DBName + ']'
set @SQL_String1 = @SQL_String1 + @SQL_String2
EXEC sp_executesql @SQL_String1;
--
insert into @Column_Info
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
CLOSE DB_cursor;
Deallocate DB_cursor;
---
select * from @Column_Info order by 2,3
----------------------------
end
---------------------------
Below is the Second script..
---------------------------
--- 2nd example (works) ---
---------------------------
-- This is by far the best/fastes of the lot for what it delivers.
--Select * into dbo.hanktst From Master.INFORMATION_SCHEMA.COLUMNS
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
----------------------------------------
--FileID: SCRIPT_Get_Column_info_(INFORMATION_SCHEMA.COLUMNS).sql
-- Utility to find all columns in all databases or find specific with a like statement
-- Look at this line to find a: --> set @SQL_String2 = ' select * into ##Temp_Column_Info....
----------------------------------------
---
SET NOCOUNT ON
begin
Declare @hanktst TABLE (
[TABLE_CATALOG] NVARCHAR(128) NULL
,[TABLE_SCHEMA] NVARCHAR(128) NULL
,[TABLE_NAME] sysname NOT NULL
,[COLUMN_NAME] sysname NULL
,[ORDINAL_POSITION] INT NULL
,[COLUMN_DEFAULT] NVARCHAR(4000) NULL
,[IS_NULLABLE] VARCHAR(3) NULL
,[DATA_TYPE] NVARCHAR(128) NULL
,[CHARACTER_MAXIMUM_LENGTH] INT NULL
,[CHARACTER_OCTET_LENGTH] INT NULL
,[NUMERIC_PRECISION] TINYINT NULL
,[NUMERIC_PRECISION_RADIX] SMALLINT NULL
,[NUMERIC_SCALE] INT NULL
,[DATETIME_PRECISION] SMALLINT NULL
,[CHARACTER_SET_CATALOG] sysname NULL
,[CHARACTER_SET_SCHEMA] sysname NULL
,[CHARACTER_SET_NAME] sysname NULL
,[COLLATION_CATALOG] sysname NULL
,[COLLATION_SCHEMA] sysname NULL
,[COLLATION_NAME] sysname NULL
,[DOMAIN_CATALOG] sysname NULL
,[DOMAIN_SCHEMA] sysname NULL
,[DOMAIN_NAME] sysname NULL
)
Declare
@DBName sysname
,@SQL_String2 nvarchar(4000)
,@TempRowCnt varchar(20)
,@Dbug bit = 0
Declare DB_cursor CURSOR
FOR
SELECT name FROM sys.databases
WHERE STATE = 0
-- and Name not IN ('master','msdb','tempdb','model','DocxPress')
and Name not IN ('msdb','tempdb','model','DocxPress')
Open DB_cursor
Fetch next from DB_cursor into @DBName
While @@FETCH_STATUS = 0
begin
set @SQL_String2 = ' select * into ##Temp_Column_Info from [' + @DBName + '].INFORMATION_SCHEMA.COLUMNS
where UPPER(Column_Name) like ''COURSE%''
;'
if @Dbug = 1 Select @SQL_String2 as '@SQL_String2';
EXEC sp_executesql @SQL_String2;
insert into @hanktst
select * from ##Temp_Column_Info;
drop table ##Temp_Column_Info;
Fetch next From DB_cursor into @DBName
end
select * from @hanktst order by 4,2,3
CLOSE DB_cursor;
Deallocate DB_cursor;
set @TempRowCnt = (select cast(count(1) as varchar(10)) from @hanktst )
Print ('Rows found: '+ @TempRowCnt +' end ...')
end
--------
Upvotes: 3
majestzim
Reputation: 528
Normally I try to do whatever I can to avoid the use of cursors, but the following query will get you everything you need:
--Declare/Set required variables
DECLARE @vchDynamicDatabaseName AS VARCHAR(MAX),
@vchDynamicQuery As VARCHAR(MAX),
@DatabasesCursor CURSOR
SET @DatabasesCursor = Cursor FOR
--Select * useful databases on the server
SELECT name
FROM sys.databases
WHERE database_id > 4
ORDER by name
--Open the Cursor based on the previous select
OPEN @DatabasesCursor
FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert the select statement into @DynamicQuery
--This query will select the Database name, all tables/views and their columns (in a comma delimited field)
SET @vchDynamicQuery =
('SELECT ''' + @vchDynamicDatabaseName + ''' AS ''Database_Name'',
B.table_name AS ''Table Name'',
STUFF((SELECT '', '' + A.column_name
FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS A
WHERE A.Table_name = B.Table_Name
FOR XML PATH(''''),TYPE).value(''(./text())[1]'',''NVARCHAR(MAX)'')
, 1, 2, '''') AS ''Columns''
FROM ' + @vchDynamicDatabaseName + '.INFORMATION_SCHEMA.COLUMNS B
WHERE B.TABLE_NAME LIKE ''%%''
AND B.COLUMN_NAME LIKE ''%%''
GROUP BY B.Table_Name
Order BY 1 ASC')
--Print @vchDynamicQuery
EXEC(@vchDynamicQuery)
FETCH NEXT FROM @DatabasesCursor INTO @vchDynamicDatabaseName
END
CLOSE @DatabasesCursor
DEALLOCATE @DatabasesCursor
GO
I added a where clause in the main query (ex: B.TABLE_NAME LIKE ''%%'' AND B.COLUMN_NAME LIKE ''%%'') so that you can search for specific tables and/or columns if you want to.
Upvotes: 3
eftpotrm
Reputation: 2271
Minor refinement on KM's solution for those like me who've got collation fun on their DB server....
DECLARE @SQL varchar(max)=''
SELECT @SQL=@SQL+'UNION
select
'''+d.name +'.''+sh.name+''.''+o.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.name COLLATE SQL_Latin1_General_CP1_CI_AS,c.column_id
from '+d.name +'.sys.columns c
inner join sys.objects o on c.object_id=o.object_id
INNER JOIN 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)
(Still living in hope that we'll find a way to do this that can be wrapped into a view.)
Upvotes: 1
Mehran
Reputation: 329
SELECT *
FROM information_schema.columns
WHERE column_name = 'My_Column'
You must set your current database name with USE [db_name] before this query.
Upvotes: 28
Jeremy
Reputation: 4838
Why not use
Select * From INFORMATION_SCHEMA.COLUMNS
You can make it DB specific with
Select * From DBNAME.INFORMATION_SCHEMA.COLUMNS
Upvotes: 36
Related Questions
- Find all tables containing column with specified name
- Get all table names of a particular database by SQL query?
- List names of all tables in a SQL Server 2012 schema
- SQL Query to list all tables+columns in a specific database/server?
- SQL Server : Search for a string across all the databases and list all the databases, tables and corresponding columns
- Query to get the names of all tables in SQL Server 2008 Database
- How to get all tables and column names in SQL?
- Find a column name from all tables and all databases with specified column name SQL Server
- Find table name in all objects of all databases
- Query Field Names Of All Tables