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 (https://blog.sqlauthority.com)
191 Comments. Leave new
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
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”
Sorry, the server where I tried to execute this query is a ….. 2000 version. Thanks for your work
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
You should make use of Generate Script option from Management studio
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%’
Thanks . It was very helpful.
thanks a lot…
Perfect… just what I was looking for…
just saved me a ton of time, thanks!
Ya scoyne..
I used this query n number of times…
Thanks a lot. very helpful
select c.name, c.id, o.name from syscolums c join sysobjects o on o.id=c.id where c.name like ‘%employee%’
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
Hi mahi,
Chekout the column “TABLE:CATALOG” in the information_schema.tables view.
SELECT * from information_schema.tables
Hope This Helps
/Glenn
The column is TABLE_CATALOG and not TABLE:CATALOG
Sorry for the typo
/Glenn
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
Available databases
EXEC sp_databases
Available tables
EXEC sp_tables
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
You can also use
select * from information_schema.columns
where column_name like 'empid'
Thanks!
Thanks @ Pushpa. In general the suggestions are really good on this site. I am a new SQL stored proc guy and trying to learn the whole logic around stored proc.
I would love to connect with any of you on LinkedIn. Send me an email request at DanishJaff. I am using Gmail.
Thank You.
Danish
how we can see specific column in sql database?
select column_name,* from information_schema.columns
where table_name = ‘Table_Name’
Can anyone tell how to find the list of tables in a database server.??
Thank u in advance……..!!!!!!!!
EXEC sp_tables
select * from INFORMATION_SCHEMA.tables
here u can get list of tables present in the database server….!!!
you are awesome !
Hi Suresh,
Try
select * From sys.tables
select * From sysobjects where type=’u’
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
Thanks so much! That is very helpful.
Thanks a Lot…!!!!
Maby easier way with more data:
SELECT *
FROM Information_Schema.Columns
WHERE COLUMN_NAME like ‘%YOURCOLUMN%’
Yes. Also you dont need to query on system tables directly
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