Today I am going to show you how do we obtain schema information in SQL Server 2005/2008. If you want to select all table names from a specific schema you need to use sys.tables and sys.schemas tables. For example I wanted to select all tables from schema bob:

1
2
3
4

SELECT t.*
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = 'bob'

Another way to do this is by using INFORMATION_SCHEMA view:

1
2
3

SELECT  *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'bob'

Lets say now that we want to select all columns from a specific table the name of which matches ‘%empl%’

1
2
3
4

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'bob'
AND COLUMN_NAME LIKE '%empl%'