Sys objects

[DaveL]

i have Several Databases with the Same view in them (vCustomers)

I have to do work on these views (drop , modify etc) depending on the Data
Sent to us via partners

i have a Master database ( mymaster) well say

and 30 other databases representing customer data

i want to put a proc in Mymaster and query any one of the other databasses
for view (vcustomers)

and im having problems referencing the Correct Database

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vCustomers]')
AND type in (N'V'

thanks DAveL

[Plamen Ratchev]

You can just prefix the objects with the correct database name:

SELECT *
FROM Database.sys.objects
WHERE object_id = OBJECT_ID(N'[Database].[dbo].[Partients]')
AND type = 'V';

--
Plamen Ratchev
http://www.SQLStudio.com

[DaveL]

this works fine if the Database name is a Literal

because im trying to make a Genric proc...i have to make dynamic sql
and im trying to Stay away from that

is there a way to make the below work without making it completly Dynamic

DAveL

"Plamen Ratchev" <Pla...@SQLStudio.com> wrote in message
news:JtSdnbOPxLTfH9zX...@speakeasy.net...

[Plamen Ratchev]

You may be able to use the undocumented (read not supported) sp_MSforeachdb procedure:
http://www.dbazine.com/sql/sql-articles/larsen5

Other than that read Erland's article here:
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB

[Erland Sommarskog]

DaveL (dvs...@sbcglobal.net) writes:
> this works fine if the Database name is a Literal
>
> because im trying to make a Genric proc...i have to make dynamic sql
> and im trying to Stay away from that
>
> is there a way to make the below work without making it completly Dynamic

I have a discussion of your situation here:
http://www.sommarskog.se/dynamic_sql.html#Dyn_DB

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx