SQL Server Select Table information

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:

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:

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%’

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

About cmanios

programming
This entry was posted in Databases, Sql Server and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s