How to get metedata info on anothe db table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to get metedata info on anothe db table

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2007-11-16 : 12:22:47

Hi,

If I run the following query I get result as 0 for IsAutoStatistics and other metadata columns:
select distinct a.name
,INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics')
, INDEXPROPERTY(a.id, a.name, 'IsClustered')
, INDEXPROPERTY(a.id, a.name, 'IsHypothetical')
,a.id
from sysindexes a,sysobjects d
where a.id=d.id and d.xtype='U' and d.name = 'tab1’
ind1 0 0 0 367340373
ind2 0 0 0 367340373

But If I run the query from db_1 database on table of db_2 database, IsAutoStatistics and other metadata columns returns null value
select distinct a.name
,INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics')
, INDEXPROPERTY(a.id, a.name, 'IsClustered')
, INDEXPROPERTY(a.id, a.name, 'IsHypothetical')
,a.id
from db_2..sysindexes a,db_2..sysobjects d
where a.id=d.id and d.xtype='U' and d.name = 'tab1’
ind1 NULL NULL NULL 1032143218
ind2 NULL NULL NULL 1032143218

What permission’s are required to run the query on db_2 database from db_1 database and get the not null results or Is there any other way we can get the required results?

Thanks

--Rubs

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-16 : 12:34:40

INDEXPROPERTY is looking in the database the query is run from, but you are passing in ID values from a different database. you could use dynamic sql, but that may not be the best choice. why are you running this query from another database? what are the results being used for?

rubs_65
Posting Yak Master

144 Posts

Posted - 2007-11-16 : 12:50:53

Our application works on 1 database but we are using partition view where each underlying table is in different database so when we need to create new partition we look at default existing partition to see what needs to be created on new database and this query is part of big query that tells us what indexes to create on new table in new partition.
How can we do this as dynamic query to solve this issue as you mentioned?

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-16 : 12:55:21

how about creating a view in each database and create a master view to combine the views from each database?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources