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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy