On daily basis, we come across this scenario when we have to find out if a column exists for
a Table or View in Database. Specially if we are debugging SQL Server Reports/An application,
we take a column name or part of it and want to know that from which table this column is used.
We can query INFORMATION_SCHEMA.COLUMNS view to get this information.
USE Test GO--Create Test Table
CREATE TABLE dbo.Test ( ID INT IDENTITY(1, 1), Name VARCHAR(100), Address VARCHAR(100) ) GO
--Create Test View
CREATE VIEW dbo.vw_TestAS SELECT ID, NAME, Address FROM dbo.Test --Check if any of the object( Table,View) has column name='Address'
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='ADDRESS'
Or If you want to search with some part of column nameSELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ADD%'
Fig 1: How to Check if Column Exists for a SQL Server Table Or View in SQL Server Database
Video Demo for Information_Schema.Columns System View
No comments:
Post a Comment