April 6, 2009 at 9:20 am
Hello,
I am working on converting data from a SQL 2005 database with hundreds of tables. Many of the tables contain very little information. I think it would greatly expedite my search for useful data if I could see a list of table names with the number of rows that each table contains. Does anyone know of a good query to use to display this information? Otherwise, is there any free (or trial-based) 3rd party software that will display this information? In general, I'm looking for a way to display information from the database so that I can find the tables that contain useful information without having to look through every table. If anyone has any other suggestions on how I may do this, I would greatly appreciate it.
Thanks.
April 6, 2009 at 9:54 am
it can all be done in a TSQL query
this query might not be correct, look up the reserved tablenames in the master database
select max(s.name), count(c.columns) from sysobjects s inner join syscolumns c on s.name = c.name where type='table'
this query is wrong but im getting u a start, maybe a guru here can type it up in a jiffy
April 6, 2009 at 10:04 am
Hey, thanks for your reply.
I actually talked to a colleague about this, and he was able to tell me a good way to do it. I'm including the query below in case it is useful to someone else:
sp_msforeachtable 'select count(*) as NumRows, "?" as table_name from ?'
Thanks.
April 6, 2009 at 10:19 am
If you just want a quick and dirty, query sys.partitions
select object_name(object_id) AS TableName, sum(rows) AS TotalRows from sys.partitions
where index_id in (0,1)
group by object_id
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 10:21 am
Hi John,
Best option is what Gail has suggested.
April 6, 2009 at 10:23 am
xgcmcbain (4/6/2009)
select max(s.name), count(c.columns) from sysobjects s inner join syscolumns c on s.name = c.name where type='table'
That'll get the number of columns, not of rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 11:53 am
I always use this:
select rowcnt from sys.sysobjects O JOIN sys.sysindexes I on O.id = I.id and indid = 1 where type = 'U' and name = 'TABLENAME'
The probability of survival is inversely proportional to the angle of arrival.
April 6, 2009 at 11:53 am
ohps sorry i wrote that too fast
April 6, 2009 at 2:15 pm
sturner (4/6/2009)
I always use this:select rowcnt from sys.sysobjects O JOIN sys.sysindexes I on O.id = I.id and indid = 1 where type = 'U' and name = 'TABLENAME'
That works, but bear in mind that sysobjects and sysindexes are deprecated, backward compatibility views that will be removed in future versions of the product.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2009 at 2:17 pm
In SQL Server 2005 SP2 and greater, you can always run the report 'Disk Usage by Table' to get the results you are looking for.
Just a thought...:)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 6, 2009 at 2:23 pm
SQL Server 2008 client tools gives you this information for both SQL 2005 and SQL 2008. Below is the query it uses. You can modify it to retrieve the information you are looking for.
declare @PageSize float
select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
SELECT
'Server[@Name=' + quotename(CAST(serverproperty(N'Servername') AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(tbl.schema_id),'''') + ']' AS [Urn],
tbl.name AS [Name],
SCHEMA_NAME(tbl.schema_id) AS [Schema],
CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit) AS [IsSystemObject],
tbl.create_date AS [CreateDate],
stbl.name AS [Owner],
ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id),0.0) AS [DataSpaceUsed],
ISNULL((select @PageSize * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id = tbl.object_id),0.0) AS [IndexSpaceUsed],
ISNULL( ( select sum (spart.rows) from sys.partitions spart where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
FROM
sys.tables AS tbl
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))
WHERE
(CAST(
case
when tbl.is_ms_shipped = 1 then 1
when (
select
major_id
from
sys.extended_properties
where
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
is not null then 1
else 0
end
AS bit)=0)
ORDER BY
[Schema] ASC,[Name] ASC
April 7, 2009 at 1:39 am
I have 19 servers and various databases. One of my monitoring is that and a little more. I use powershell and SMO to do this, they return to me this information in all servers and databases.
requires a little progamming but is very powerful. I use this script :
create table tableinf (
DDate datetime default getdate(),
ServerName varchar(50),
DatabaseName varchar(50),
TableName varchar(50),
SpaceIndexUsed float,
SpaceDataUsed float,
Total as (SpaceIndexUsed + SpaceDataUsed),
RRowCount int
)
Scritp PS
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
# Database and server repository
$ServidorCentral = "SERVER1"
$DatabaseCentral = "DBA"
#Today date
$Datahoje = get-date -format "yyyy-MM-dd hh:mm:ss"
foreach ($svr in get-content "C:\servers\servers.txt" )
{
$Servidor=New- Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
$data = $Servidor.Databases| where-object {$_.IsSystemObject -eq $FALSE -and
$_.IsAccessible -eq $TRUE -and $_.name -ne "DBA" } | foreach {
$DatabaseName = $_.name
$ServerName = $Servidor.Name
foreach ($tabelas in $Servidor.Databases[$_.name].tables ) {
if (!$tabelas.IsSystemObject)
{
$tablename = $tabelas.name
$SpaceIndexUsed = $tabelas.IndexSpaceUsed
$SpaceDataUsed = $tabelas.DataSpaceUsed
$tabelas.RowCount
$sql = "insert into TableInf(DDate,ServerName,DatabaseName,TableName,SpaceIndexUsed,SpaceDataUsed,RowCount) values ('$DataHoje','$ServerName','$DatabaseName','$TableName',$SpaceIndexUsed,$SpaceDataUsed,$RowCount)"
Invoke-Sqlcmd -ServerInstance $ServidorCentral -Database $DatabaseCentral -Query $sql
}
}
}
}
Now the data
select * from TableInf
I have a Database and Server Repository (DBA and SERVER1) where i save this information. This scripts works fine with SQLPS.exe (SQL2008), bu you can use in 2005 ands 2000 if use poweshell shell and have to load sql server snapins. The servers.txt have all my servers (foreach ($svr in get-content "C:\servers\servers.txt" ))
April 7, 2009 at 1:39 am
Perhaps a bit more information as well as the rowcount would help... and, yes, I used deprecated views so that it's backwards compatible with 2k...
[font="Courier New"]/**********************************************************************************************************************
Purpose:
Returns a single result set similar to sp_Space used for all user tables at once.
Notes:
1. May be used as a view, stored procedure, or table-valued funtion.
2. Must comment out 1 "Schema" in the SELECT list below prior to use. See the adjacent comments for more info.
Revision History:
Rev 00 - 22 Jan 2007 - Jeff Moden
- Initital creation for SQL Server 2000
Rev 01 - 11 Mar 2007 - Jeff Moden
- Add automatic page size determination for future compliance
Rev 02 - 05 Jan 2008 - Jeff Moden
- Change "Owner" to "Schema" in output. Add optional code per Note 2 to find correct schema name
**********************************************************************************************************************/
--===== Ensure that all row counts, etc is up do snuff
-- Obviously, this will not work in a view or UDF and should be removed if in a view or UDF. External code should
-- execute the command below prior to retrieving from the view or UDF.
DBCC UPDATEUSAGE(0)
--===== Return the single result set similar to what sp_SpaceUsed returns for a table, but more
SELECT DBName = DB_NAME(),
SchemaName = SCHEMA_NAME(so.UID), --Comment out if for SQL Server 2000
--SchemaName = USER_NAME(so.UID), --Comment out if for SQL Server 2005
TableName = so.Name,
TableID = so.ID,
MinRowSize = MIN(si.MinLen),
MaxRowSize = MAX(si.XMaxLen),
ReservedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB,
DataKB = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
+ SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
IndexKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.DPages ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN ( 255) THEN ISNULL(si.Used,0) ELSE 0 END) * pkb.PageKB,
UnusedKB = SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Reserved ELSE 0 END) * pkb.PageKB
- SUM(CASE WHEN si.IndID IN (0,1,255) THEN si.Used ELSE 0 END) * pkb.PageKB,
Rows = SUM(CASE WHEN si.IndID IN (0,1 ) THEN si.Rows ELSE 0 END),
RowModCtr = MIN(si.RowModCtr),
HasTextImage = MAX(CASE WHEN si.IndID IN ( 255) THEN 1 ELSE 0 END),
HasClustered = MAX(CASE WHEN si.IndID IN ( 1 ) THEN 1 ELSE 0 END)
FROM dbo.SysObjects so,
dbo.SysIndexes si,
(--Derived table finds page size in KB according to system type
SELECT Low/1024 AS PageKB --1024 is a binary Kilo-byte
FROM Master.dbo.spt_Values
WHERE Number = 1 --Identifies the primary row for the given type
AND Type = 'E' --Identifies row for system type
) pkb
WHERE si.ID = so.ID
AND si.IndID IN (0, --Table w/o Text or Image Data
1, --Table with clustered index
255) --Table w/ Text or Image Data
AND so.XType = 'U' --User Tables
AND PERMISSIONS(so.ID) <> 0
GROUP BY so.Name,
so.UID,
so.ID,
pkb.PageKB
ORDER BY ReservedKB DESC
[/font]
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
--Jeff Moden
Change is inevitable... Change for the better is not.
April 8, 2009 at 2:24 am
I have a script that I use. Basically it loops through all the tables in a database and uses
exec sp_spaceused
This then returns
TableName
, NumberOfRows
, ReservedSpace
, DataSpace
, IndexSize
, UnusedSpace
The advantage of this is speed over anything with count(*) in, the disadvantage is that it does not work on views. If you google it you may find more details and possibly other peoples scripts using this command.
Regards,
Mark
April 8, 2009 at 2:53 pm
Ells (4/8/2009)
I have a script that I use. Basically it loops through all the tables in a database and usesexec sp_spaceused
This then returns
TableName
, NumberOfRows
, ReservedSpace
, DataSpace
, IndexSize
, UnusedSpace
The advantage of this is speed over anything with count(*) in, the disadvantage is that it does not work on views. If you google it you may find more details and possibly other peoples scripts using this command.
Regards,
Mark
So... why not post the script. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply