August 12, 2011 at 12:48 pm
select name from AdventureWorksDW.sys.columns c
where c.object_id=object_id('dbo.DIMPRODUCT')
This query returns null. Why is that?
I tried four part naming with the below query:
select name from [WW\ENTERPRISE2008].AdventureWorksDW.sys.columns c
where c.object_id=object_id('dbo.DIMPRODUCT')
This does not work .
August 12, 2011 at 1:18 pm
The following would be a solution:
WITH
Object AS
(SELECT o.object_id as ObjectID
, s.name as SchemaName
, o.name as ObjectName
FROM AdventureWorksDW.sys.objects as o
LEFT OUTER JOIN AdventureWorksDW.sys.schemas as s ON s.schema_id = o.schema_id)
SELECT *
FROM AdventureWorksDW.sys.columns c
JOIN Object ON Object.ObjectID = c.object_id
WHERE SchemaName + '.' + ObjectName = 'dbo.DIMPRODUCT'
The issue is object_name is being ran against he current database that you are in. I thought I could do something like AdventureWorksDW.dbo.object_name('dbo.DIMPRODUCT'). However, it doesn't like the multipart name.
August 12, 2011 at 1:19 pm
Two things. One, from what database are you trying torun this query.
Two, have you tried running from within AdventureWorksDW?
August 12, 2011 at 1:21 pm
I think your connection needs to be on that specific Database , so use the USE AdventureWorksDW and then execute your select statement.
When I go back and forth in the same same instance connecting from one database to the other, if I am not on AdventureWorksDW, I get nothing as well.
I think it has to do with quering sys tables, but I cannot pin down a specific article about it. :hehe:
If [WW\ENTERPRISE2008].dbo AdventureWorksDW is a linked server , I don't think you will be able to use distributed queries on a sys table like that.
I would copy AdventureWorksDW from one instance to the other.
If you are using SQL Server 2008 r2, there is a really easy copy feature.
August 12, 2011 at 1:26 pm
If you look up OBJECT_ID in Books Online, you will notice you can also provide the database name as well as the schema name.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply