hi all,

i was using the system tables namely sysindexes and sysobjects  in sql server 2000. But now sql server 2005 is using instead of sql server 2000.  Since am using sysindexes and sysobjects, too much time is taking for the execution in sql server 2005. So I need to change the query suitable for sql server 2005.

I have read in msdn that the system tables are replaced with corresponding catalog views in sql server 2005. The catelog view for sysobjects is sys.objects. plz check the link  http://technet.microsoft.com/en-us/library/ms187997.aspx

Could anyone please tell me how to replace the sysindexes and sysobjects in the query without rewriting the query too much. If I can replace sysobjects with just sys.objects, then it will be very helpful. Is it possible? And what about sysindexes.  Any help anyone could give would be greatly appreciated!

For SQL Server 2005 you can replace sysobjects with sys.objects and sysindexes with sys.indexes.

Regards

i have replaced sysobjects with sys.objects and sysindexes with sys.indexes. but i need to use rows property of sysindexes. In sys.indexes, there is no such rows prperty.. but its there in sys.partitions. but how can i connect these two? when i used sys.partitions.rows instead of sysindexs.rows, am not getting the exact result.. am getting a lot of more rows.. can anyone please tell me how to use it?

select sum(rows) as rows
from sys.indexes si
join sys.partitions sp on sp.[object_id] = si.[object_id]
                      and sp.index_id = si.index_id
                      and si.index_id in (0,1)
                      and si.object_id = object_id('<YOUR TABLE HERE')

-- Armando 

Hi,

thanks for the reply. Here am pasting the query. In this query,  id can be replaced with object_id and indid can be replaced with index_id. 

(select m.addressbook,m.firstname,si.rows as 'Row_Count' from tblmembers m,
sysobjects so,sysindexes si where si.id = so.id and si.indid in(0,1) and so.type = 'U' and so.name in
(select cast(addressbook as nvarchar(50)) from tblmembers)
and so.name=cast(m.addressbook as nvarchar(50)) and si.rows>=3500 and m.isactive=1)

Could u please tell me how to incoporate the above mentioned rows property here in this query. Thanks in advance..

 Try this.... I don't have a schema for tblMembers so you may have to tweak it.

select m.addressbook, m.firstname, sum(rows) as rows
from sys.indexes si
join sys.objects so on si.[object_id] = so.[object_id]
                      and so.type_desc = 'user_table'
join sys.partitions sp on sp.[object_id] = so.[object_id]
                      and sp.index_id = si.index_id
                      and si.index_id in (0,1)
join dbo.tblmembers m on m.addressbook = so.[name] and m.isactive = 1
group by m.addressbook, m.firstname
having sum(rows) >= 3500
order by m.addressbook
                     

 Dear friend, am getting error message like

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'tbl_taboo_imports' to data type int.

tbl_taboo_imports is a table in the database. but it has nothing to do with the above code. Then I removed so.type_desc = 'user_table' and executed, then i got the similar error message with a different table name. Have u got any idea?

I'd need to see a schema and sample data for tblmembers. I think you're pretty much close.  It has nothing to do with so.type_desc.

Thanks my dear friend.. please have a look into this case..

My requirement is:
I have a table named tblmembers. some of the fields are addressbook, emailaddress, firstname, hasaddressbook..
select addressbook from tblmembers where hasaddressbook=1
the above query returns more than 4000 records. And I have that much tables with tablename as [addressbook]
for example, i have tables like [546],[123],[63],[3345], etc..
i need to find out how many such tables are having more than 3500 rows.
Then i can return their corresponding name, emailaddress etc from tblmembers

I have used system tables like sysobjects and sysindexes for this purpose. Its working fine in local dbserver. But its taking more than 1 hr to execute the query in the production. I thought its because of sysobjects and sysindexes.. And the corresp. things in sql server 2005 is sys.objects and sys.indexes. so i just tried to use sys.objects and sys.indexes with ur help.. but am stuck. i think now u got a better picture..

Do u have any other idea to get this done with or without system tables..  Thanks a lot..

Again, it's tough to know what the problem is without knowing what the table looks like. For instance, is addressbook an int?  Is it a varchar? 

 I can help you better if you provide a create table statement and a  couple of rows of sample data.

addressbook is an int and the table contains some nvarchar type fields also..

i have just posted the actual scenario. Could you please have a look into it.  This will make things easy i think..

Thanks

Ratheesh 

if address book is an int, then it needs to be casted to make the join work.  Unfortunately,
this is going to cause a table scan. 

select m.addressbook, m.firstname, sum(rows) as rows
from sys.indexes si
join sys.objects so on si.[object_id] = so.[object_id]
                      and so.type_desc = 'user_table'
join sys.partitions sp on sp.[object_id] = so.[object_id]
                      and sp.index_id = si.index_id
                      and si.index_id in (0,1)
join dbo.tblmembers m on cast(m.addressbook as varchar) = so.[name] and m.isactive = 1
group by m.addressbook, m.firstname
having sum(rows) >= 3500
order by m.addressbook

 Thanks dear.. that error is gone.. If this does table scan, then a lot of time will be taken as i have thousands of tables.. right?

Could u please tell me onething.. which choice will be faster..

1)using sysobjects and sysindexes or

2)using sys.objects and sys.indexes and sys.partitions ( this has some join for getting the row count also)

 right now sysobjects and sysindexes are being used. Its working fine in local dbserver. But its taking too much time when executing in the production dbserver. I guessed  it is due to these system tables. so i requested ur help to rewrite the code with  sys.objects and sys.indexes and sys.partitions. If we go in this way, will it take more time? 

 or  Could you please have a guess.. what may be the problem behind this. Is it really due to system tables?

or If the size of the system tables is large, then this time out happens?? 

You're at the mercy of your data model.  If addressbook represents a table name, perhaps then it should've been declared as a varchar column (or char column it's only a few bytes).

 If the existing query is taking a long time in production, perhaps sysobjects is getting locked.  Try a WITH (NOLOCK) hint on the sysobjects table.

 Also, I'd recommend using sys.indexes and sys.objects under SQL 2005.  The old sysobjects and sysindexes may be gone in a future version of SQL Server.

 Thanks dear.. How can i check whether the sysobjects table already has this WITH (NOLOCK) hint or not? Could u please guide me how to give WITH (NOLOCK) hint on the sysobjects table?  To be frank.. this is the first time am hearing this!! sorry to disturb u again..