Hello I have 10 different databases on my server and each are replica for different testers.Assume that my db names are A1 to A10

Now If I run following query On Master database

use Master

select 'A1',* from A1.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';
select 'A2', * from A2.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';

then I am getting nothing as o/p


If I run same query from database A1 like below

use A1

select 'A1',* from A1.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';
select 'A2', * from A2.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';

then I am getting o/p for A1 but not for A2

If i run same query on database A2 like below

use A2

select 'A1',* from A1.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';
select 'A2', * from A2.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';

then I am getting o/p for A1 but not for A1

If i run same query on Database A3 like below

use A3

select 'A1',* from A1.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';
select 'A2', * from A2.sys.columns  where object_id = object_id('vourow') and name  ='VoucherRowNotToFA';

then I am getting o/p for A1 but not for A2

So using A3 why it is showing A1 DB output and not for A2

Hi

You must use dynamic query.

DECLARE @sql varchar(max),@dbname varchar(10)
DROP TABLE #temp
CREATE TABLE #temp (dbname varchar(10),tb_name varchar(100),o_id bigint,u_type int)
DECLARE @db TABLE (dbname varchar(10))
INSERT @db VALUES ('A1'),('A2'),('A3')
SET @dbname = 'A1'
WHILE @dbname in (SELECT dbname FROM @db )
BEGIN
SELECT top 1 @dbname = dbname FROM @db
SET @sql = 'USE '+@dbname+CHAR(10)+' INSERT #temp select '''+@dbname+''',name,object_id,type from sys.columns  where object_id = object_id(''vourow'') and name  =''VoucherRowNotToFA'''
PRINT @sql
DELETE FROM @db
WHERE dbname = @dbname
SELECT top 1 @dbname = dbname FROM @db
END

• Edited by Vitaliy Lukashev 19 minutes ago

My question is different.My question is that Why I am able to  get A1 o/p but not A2 o/p If I use A3 database

 If you have not already as we can see , try specifying the full three part name for the object and make sure if you have a case sensitive collation that you spelled the entire name correctly.

SELECT * FROM dbname.sys.columns WHERE object_id=object_id('dbname..tblname')

Hello Aminesh - I simulated your scenario in my environment & this is what you can use:

declare @tblData table ( dbname varchar(100), obj_id int, name varchar(100),
column_id int, max_lenth int )

insert into @tblData
exec sp_msforeachdb
'use [?];
select ''?'' as dbname, object_id, name, column_id, max_length from [?].sys.columns where 
object_id = object_id(''test82'') and name  =''empid'' '

select * from @tblData

Note: I created Test82 table in two databases with column EmpID & I get two records. Also note that I have used limited columns for output, you may wish to use more columns.

Hope this helps

My question is different.My question is that Why I am able to  get A1 o/p but not A2 o/p If I use A3 database

By chance the table has the same object_id in the A1 and A3 databases.