Select only view from INFORMATION_SCHEMA

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select only view from INFORMATION_SCHEMA

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-11-20 : 03:27:57

Hello

Is there any way to get all colums related Tables or view separately
If I do a

SELECT * FROM INFORMATION_SCHEMA.Colums

I get all columns included those related to views (5800 rows)
if I do

SELECT *
FROM vinopressbe.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME not in (select TABLE_NAME FROM vinopressbe.INFORMATION_SCHEMA.VIEWS)

I guess I get all columns related to Tables (1900 rows)

but if I do

SELECT *
FROM vinopressbe.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME not in (select TABLE_NAME FROM vinopressbe.INFORMATION_SCHEMA.TABLES)

I get nothing !
What am I doing wrong ?
Is there any better way to do it ?

Thanks for any help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-20 : 08:23:00

try

SELECT *
FROM vinopressbe.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME not in (select TABLE_NAME FROM vinopressbe.INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE_TABLE')

or maybe
SELECT *
FROM vinopressbe.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in (select TABLE_NAME FROM vinopressbe.INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-11-21 : 02:05:48

Thanks a lot nigelrivett

Indeed
INFORMATION_SCHEMA.VIEWS gives views only
INFORMATION_SCHEMA.TABLES gives TABLES AND VIEWS

So your method is the good one !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources