April 25, 2012 at 12:10 am
Hi,
i need to get all tables name,size,rows from all databases where rows > XXX
i write this script but i don't know how to filter that it will insert only tables with rows > XXX
also it is important that the database name will be include in the row insert.
can it be done with this syntax?
if not how i can achieve that?
THX
--CREATE TABLE #Tables (DBNAME sysname default DB_NAME(),TableName VARCHAR(255),rows bigint,reserved VARCHAR(255),data VARCHAR(255),index_size VARCHAR(255),unused VARCHAR(255) );
declare @dbname as varchar(128)
declare @run_command varchar(max)
DECLARE CRS_db CURSOR LOCAL FAST_FORWARD
FOR
SELECT [name]
FROM master.sys.databases where name not in ('master','model','tempdb','msdb','distribution')
OPEN CRS_db
FETCH NEXT FROM CRS_db INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @run_command = 'use [' + @dbname + '] EXEC sp_MSforeachtable ''INSERT INTO #Tables (TableName,rows,reserved,data,index_size,unused) EXEC sp_spaceused ''''?'''''''
exec( @run_command)
FETCH NEXT FROM CRS_db INTO @dbname
END
CLOSE CRS_db
DEALLOCATE CRS_db
April 25, 2012 at 12:34 am
The links below may help. You can add a WHERE clause to meet your requirements for the number of rows.
Query to list number of records in each table in a database
See the selected answer in the above link.
Query to find number Rows, Columns, ByteSize for each table in the current database – Find Biggest Table in Database[/url]
======================================
Blog: www.irohitable.com
April 25, 2012 at 12:43 am
how can i use the WHERE clause inside my query to not go over all tables,only tables with rows > 1000?
April 25, 2012 at 12:49 am
Mad-Dog (4/25/2012)
how can i use the WHERE clause inside my query to not go over all tables,only tables with rows > 1000?
Seriously? How about adding "rows > 1000" (assuming you followed Rohit's link)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 25, 2012 at 1:06 am
i thought that i can use the WHERE inside my query along with sp_msforeachtable and not go over all the tables.
April 25, 2012 at 1:11 am
OK Guys.
This code returns all tables and total rows in each table. I also used WHERE clause:
CREATE TABLE #temp
(
Table_Name VARCHAR(255),
Total_Rows INT
)
INSERT #temp
EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1),
COUNT(*) FROM ? WITH (NOLOCK)'
SELECT Table_Name, Total_Rows
FROM #temp where Total_Rows > 1000
ORDER BY Table_Name
DROP TABLE #temp
======================================
Blog: www.irohitable.com
April 25, 2012 at 1:23 am
my problem is that i don't want to insert all the tables in the first place only the one with rows > 100000 because i've 4000 tables in every database and i've lots of databases.
April 25, 2012 at 1:23 am
@Phil:
I added WHERE clause in the script mentioned in the first link of my post. After adding the WHERE clause, it looks like this:
SELECT
t.NAME AS TableName,
i.name as indexName,
sum(p.rows) as RowCounts,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
p.rows > 1000 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
object_name(i.object_id)
======================================
Blog: www.irohitable.com
April 25, 2012 at 1:25 am
I think the second solution I posted (with small script) will do the job.
======================================
Blog: www.irohitable.com
April 25, 2012 at 6:43 am
Below script should do the work for you:
IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '%#TableSize%')
BEGIN
DROP TABLE #TableSize
END
GO
CREATE TABLE #TableSize
(
name sysname,
[rows] INT,
reserved VARCHAR (200),
data VARCHAR (200),
index_size VARCHAR (200),
unused VARCHAR (200),
DatabaseName sysname DEFAULT(DB_NAME())
)
GO
EXECUTE sp_msforeachtable 'INSERT INTO #TableSize (name,rows,reserved,data,index_size,unused) EXECUTE sp_spaceused [?] '
GO
SELECT TS.[name],(cast (substring(TS.reserved,0,len(TS.reserved)-2) as INT)/1024) AS TableSizeInMB
FROM #TableSize TS
WHERE TS.[rows]>1000
ORDER BY TS.[name]
April 25, 2012 at 10:52 pm
THX but i've manage to work this out with another script.
what i want to achieve is to insert all the tables with amount of rows in it and to insert all the tables and then filter it.
this script gives me the same results as sp_spaceused in the rows and in the reserved columns and filter the results by rows
SELECT
t.NAME,
p.[Rows],
(sum(a.total_pages) * 8) / 1024
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.type = 'U'
AND p.[rows] > 10000
--AND i.OBJECT_ID > 255
-- AND i.index_id <= 1
GROUP BY
t.NAME,p.[Rows]
ORDER BY t.NAME ASC
April 27, 2012 at 12:38 am
Rohit, you need to join allocation_units on the correct columns, let me find my script and post it back shortly
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 27, 2012 at 4:43 am
use this
selectobject_name(i.object_id) as TableName
, ISNULL(i.name, 'HEAP') as IndexName
, i.index_id as IndexID
, i.type_desc as IndexType
, p.partition_number as PartitionNo
, p.rows as NumRows
, au.type_desc as InType
, au.total_pages as NumPages
, au.total_pages / 128 as TotMBs
, au.used_pages / 128 as UsedMBs
, au.data_pages / 128 as DataMBs
from sys.indexes i inner join sys.partitions p
on i.object_id = p.object_id and i.index_id = p.index_id
inner join sys.allocation_units au on
case
when au.type in (1,3) then p.hobt_id
when au.type = 2 then p.partition_id
end = au.container_id
where object_name(i.object_id) not like 'sys%'
and object_name(i.object_id) <> 'dtproperties'
and p.rows > 0
order by TableName, IndexID
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply