information_shema cross server?

[DLu]

Hi All:

When I check out > 2000 customer databases cross 8 SQL 2000 servers, I wish I could use
"Select * from " + @server + "." + @databaseName + ".information_Schema.coulmns
where column_name = " + @column_name.

It did not works in this way.
However, why it works on
"Select * from serverA.master.information_schema.columns"?
It is also a cross server query but is on Master DB.

thanks
-D

[rmiao]

Because information_schema views are in master db physically, and don't think you can query that via linked server directly.

[DLu]

Right now, I have to run the dynamic script within the server,
but it looks each of the database has its own information_schema which helps me identify the completion of service pack distribution.

Thus, to cross the server, the only way is to use sysobjects which I want to avoid.

p.s. is there some properties about information_schema with stored procdure/trigger? In addition, whatelse I could do with the proc with encryption?

thanks for the info.
-D

[rmiao]

You can query information_schema views in user db like run system sp, but they are in master db. Can try create view or sp in target db on linked server then query view or run sp via linked server.