typical query
-
Hi,
I need a query which will return the tablename,and total no of records of that table name in sql server.I dont want in plsql.
Thanks,
J.Aswani kumar.
-
PL/SQL is Oracle lingo...SQL Server's language is T-SQL.
If your stats are up-to-date, then you can do this:
select substring(o.name, 1, 30) Table_Name ,i.rows Number_of_Rows
from sysobjects o
inner join sysindexes i
on (o.id = i.id)
where o.xtype = 'u'
and i.indid < 2
order by o.name
or you can also use the undocumented stored proc sp_Msforeachtable to achieve the goal. Sp_MSforeachtable scans through all the tables in a certain database and performs a command on those tables. You use "?" as a placeholder to represent the table name.
sp_msforeachtable @command1= 'print ''?''', @command2 = "select count(*) from ?"
-
use mydatabase
go
dbcc updateusage(mydatabase)
go
sp_msforeachtable @command1= 'sp_spaceused ''?'''
go
MVDBA
-
CREATE TABLE #TableUsage (
Name sysname,
Rows int,
reserved nvarchar(50),
Data nvarchar(50),
Index_Size nvarchar(50),
unused nvarchar(50)
 
EXEC sp_msforeachtable @command1= 'INSERT INTO #TableUsage exec sp_spaceused ''?'''
SELECT * FROM #TableUsage
_____________
Code for TallyGenerator -
Thanks a lot for ur answers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply