April 6, 2004 at 1:53 am
I'am using the following query in query analyser(QA) to get the table name and rows in a user defined tables.
SELECT TOP 20 db_name()[Database], OBJECT_NAME(id)[Table], Rows
FROM [Northwind].dbo.sysindexes
WHERE indid < 2
AND id in (SELECT OBJECT_ID(NAME) FROM [Northwind].[dbo].[sysobjects] WHERE type = 'U')
AND rows > 1000
ORDER BY ROWS DESC
But what its returning is - results of database which is selected in the combo box in the tool bar of the QA instead of [Northwind]
Any tips on how to make it work - I don't want to use the statement -
USE <database>
April 6, 2004 at 4:10 am
Try This
sp_MSforeachdb 'use ? SELECT TOP 20 db_name()[Database], OBJECT_NAME(id)[Table], Rows
FROM sysindexes
WHERE indid < 2
AND id in (SELECT OBJECT_ID(NAME) FROM [sysobjects] WHERE type = ''U'')
AND rows > 1000
ORDER BY ROWS DESC '
April 6, 2004 at 6:15 am
Thanks Amit,
but I'am unable to understand, why my query is not working..what might be wrong with it
April 6, 2004 at 10:12 pm
Object_id , db_name and object_name are functions that work in current database only.
that's the reason why your inner query SELECT OBJECT_ID(NAME) FROM [Northwind].[dbo].[sysobjects] WHERE type = 'U' will mostly return NULLS and hence no results in your main query.
If you write your query as following, row count will come from Northwind but the db_name and object_name will be as from current db.
SELECT TOP 20 db_name()[Database], OBJECT_NAME(i.id)[Table], Rows
FROM [Northwind].dbo.sysindexes I INNER JOIN [Northwind].[dbo].[sysobjects] O ON (O.ID = I.ID )
WHERE I.indid < 2
AND O.TYPE = 'U'
AND I.rows > 10
ORDER BY ROWS DESC
And following will give table name and rowcount from Northwind but database name to be your current database.
SELECT TOP 20 db_name()[Database], o.name [Table], Rows
FROM [Northwind].dbo.sysindexes I INNER JOIN [Northwind].[dbo].[sysobjects] O ON (O.ID = I.ID )
WHERE I.indid < 2
AND O.TYPE = 'U'
AND I.rows > 10
ORDER BY ROWS DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply