Posted by Erland Sommarskog on 05/18/06 01:25

rcamarda (robc390@hotmail.com) writes:
> but this wont work:
> select * from [cognos-dev].ds_v6_source.information_schema.tables order
> by table_name
>
> Error:
> Msg 117, Level 15, State 1, Line 1
> The object name 'cognos-dev.ds_v6_source.dbo.information_schema.tables'
> contains more than the maximum number of prefixes. The maximum is 3.

There is an apparent inconsistency between the error message and the
command you presented.

Then again, I was not able to get it to work with the proper command
either. This is probably because the INFORMATION_SCHEMA views are
not present in the databases, only in master. (Hm, I believe they
were in SQL 7, so with if you connect to SQL 7 it might work.)

Rather than using INFORMATION_SCHEMA, use the system tables or
the catalog views depending on which version of SQL Server you
are on.

SELECT name FROM [cognos_dev].ds_v6_source.dbo.sysobjects
WHERE type = 'U'
ORDER BY name



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Your Professional Advice Please - Design
• Prev in forum: Re: alter table nocheck constraint still some dependencies
• Thread view: Re: Compare databases between 2 SQL servers

[Reply to this message]