Thursday, March 8, 2018

Information Schema in Sql Server

How to get Column Names From A Table

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'employee'

get common column names from two tables. if you get confuse then get the common column names from two tables whenever you want to perform the join operation

How to get Column Names From All Data Base Tables using LIKE


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 '%inv_num%'
ORDER BY schema_name, table_name;

How to Get Common Column Names From Two Table 

select A.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS A
join INFORMATION_SCHEMA.COLUMNS B
on A.COLUMN_NAME = B.COLUMN_NAME
where A.TABLE_NAME = 'employee'
and B.TABLE_NAME = 'customer'



No comments: