based on id, how to know table name

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

General SQL Server Forums

New to SQL Server Programming

based on id, how to know table name

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-04 : 01:40:58

Dear All,
based on objectid column in sysindexes, how to know the table name?

select object_name(id) as table_name, rows from sysindexes
where indid<2 order by rows desc

actually i'm trying to replace the query
select count(*) from mytable

with
select object_name(id) as table_name, rows from sysindexes
where indid<2
query

please guide me

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-04 : 01:48:32

What did the following give?

select object_name(id) as table_name, rows from sysindexes
where indid<2 order by rows desc

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-04 : 01:58:55

select rows from sysindexes
where indid<2 and object_name(id)='mytable23'

yes i got the desired out put.

thank you madhi....
but i still got doubt how it indid taking the data......

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-04 : 02:00:30

and actually one row less it is showing..
select count(*) is giving 623
and this one is giving 622

Vinod
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-04 : 02:08:40

dbcc updateusage ('dbname','mytable23') with count_rows

select rows from sysindexes
where indid<2 and object_name(id)='mytable23'

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-04 : 02:17:31

Thank you Madhi,now it is working fine.....

actually i'm tuning stored procedures and functions in my application.
can i replace select count(*) from my table query with the above query? will it give the same result? or i need to use the DBCC command everytime? suggest me please....
and will it really better replacing count (*) with the above query......
and i'm planning to replace IN with EXISTS at each and every procedure and function.
as well as cursor with set based sql(i'l take guidence for all these things)

please guide me in this regard

thanks a lot for your help

Vinod
Even you learn 1%, Learn it with 100% confidence.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-04 : 08:31:29

if your count must be 100% accurate 100% of the time, you should use count(*)

if 100% accuracy is not needed, sysindexes is ok.

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources