Date parsed: 19/10/2007 07:32:19
Date: Fri, 19 Oct 2007 14:32:19 -0700

Someone will point out what's going on and for every issue you encounter
they'll tell you how you screwed up (though usually politely - unless you
get celko). I'll probably get blasted for this post. However, personally, I
think INFORMATION_SCHEMA sucks and that the SQL Server catalog tables are
far, far superior and thought out much better (esp. 2005).

While I'll blame Microsoft for the poor docs on it, I don't blame them for
the bulk of my issues with it as they just implimented the ANSI standard. I
just don't like it and don't think it was well thought out.

Jay

"Daniel Jameson" <danjam47@newsgroup.nospam> wrote in message
news:OpZ6jOpEIHA.936@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I wanted to write some stored procedures to help me manage my indexed
> views in SQL2000/2008. Since I wanted to follow the advice to use the
> INFORMATION_SCHEMA views instead of system tables/catalog views. I have
> this query that will run in both 2000 and 2008 and it correctly finds my
> indexed views:
>
> select *
> from sysobjects
> where type = 'V'
> and id in (select id from sysindexes);
>
> However, if I run this query in either 2000 or 2008 , it returns nothing:
>
> select * from INFORMATION_SCHEMA.VIEWS
> where TABLE_NAME in (
> select TABLE_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
> )
>
> The SQL 2008 BOL states "Returns one row for each column that is
> constrained as a key in the current database.", and SQL 2000 BOL state
> "Contains one row for each column, in the current database, that is
> constrained as a key."
>
> Does anyone have any sage advice on this topic, or should I post it as a
> Connect issue for SQL 2008?
>
> --
> Thank you,
>
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
>
>