Sunday, 18 March 2018

How to Find Particular Column from All Tables in SQL Server

It is always too hard to use the Alt + F1 to check and review if there is a column present in the created table in SQL Server. Further, it would be much easier if there was a query to find the required column was present in all tables are not. An easy query for you to find a particular column from all tables in a database.

USE SQLArena_Test
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 'Date' --Replace Date with your own column name
ORDER BY schema_name, table_name;








Now some of them would seek for the same column whether present or not across all tables and across the database from a server. We can do that as well by using stored procedure sp_msforeachdb.

EXEC sp_MSForeachdb
' USE [?]
IF ''?'' <> ''master''
   AND ''?'' <> ''model''
   AND ''?'' <> ''msdb''
   AND ''?'' <> ''tempdb''
   AND ''?'' like ''database name''    
BEGIN
   SELECT ''?''

 Use the above SELECT statement here to fetch across all your databases
  
END'

0 comments: