January 13, 2016 at 9:29 am
Hello Good Morning,
I am using sql server 2008R2, it has a database called "MetCar" it has several tables in it about 87 how can i know,
what is size of each table (in MB)
Number of rows in each table
what date it last DML happend
what store procedure they are using
for example
Table NameRows Columns Table_Created TableSizeLastUpdate_Date Using_Procedures
PharmaComp398 4 18/11/2013 440 MB 2/2/2015 msp_pharma, msp_proc2,msp_proc3
can you please advise the query
Thank you in Advance
Milan
January 13, 2016 at 10:01 am
This should do:
SELECT
name,
sizeMB = (
SELECT SUM(a.total_pages) * 8.0 / 1024
FROM sys.indexes AS i
INNER JOIN sys.partitions AS 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.OBJECT_ID = i.object_id
),
NumberOfRows = OBJECTPROPERTYEX(object_id, 'cardinality'),
NumberOfColumns = (
SELECT COUNT(*)
FROM sys.columns
WHERE object_id = t.object_id
),
t.create_date,
LastUpdateDate = (
SELECT MAX(last_user_update)
FROM sys.dm_db_index_usage_stats
WHERE object_id = t.object_id
),
StoredProcedures = STUFF((
SELECT ',' + p.name
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.procedures AS p
ON d.referencing_id = p.object_id
WHERE d.referenced_id = t.object_id
ORDER BY p.name
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,1,SPACE(0))
FROM sys.tables AS t
-- Gianluca Sartori
January 13, 2016 at 10:23 am
asita (1/13/2016)
I am using sql server 2008R2
For future reference, there is a forum specifically for SQL2008. You would be better off posting in that forum, because you are less likely to get responses that require SQL 2012 or up.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 13, 2016 at 12:01 pm
Nice quick script by Gianluca there. This is what I have been using to get as accurate a size on the tables as I could get. I have added in "metadata" options that you need as well.
/*
Purpose:
To Evaluate table sizes combined with index space consumption to determine higher cost tables in
terms of storage, resources and maintenance needs.
*/
SET NOCOUNT ON;
DECLARE @dbsize DECIMAL(19,2)
,@logsize DECIMAL(19,2)
,@IsMSShipped TINYINT = 0
/*NULL = all objects
1 = MS Shipped Objects
0 = User Created Objects
*/
,@TableLastUpdated TINYINT = 1
/* 1 = Yes
0 = No
*/
,@ColumnCount TINYINT = 1
/* 1 = Yes
0 = No
*/
,@ProcedureDependencies TINYINT = 1
/* 1 = Yes
0 = No
*/
;
--contributes TO SPACE IN what versions OF SQL
--2012(202,204,207,211,212,213,214,215,216,221,222,236)
--2008(202,204,211,212,213,214,215,216)
--2008R2(202,204,211,212,213,214,215,216)
--2014(202,204,207,211,212,213,214,215,216,221,222,236)
--2005(202,204)
--2016(202,204,207,211,212,213,214,215,216,221,222,236)
DECLARE @ServerMajorVersion DECIMAL(4, 2)
SELECT @ServerMajorVersion = CONVERT(DECIMAL(4, 2), PARSENAME(dt.fqn, 4) + '.'
+ PARSENAME(dt.fqn, 3))
FROM ( SELECT CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion'))
) dt ( fqn );
IF OBJECT_ID('tempdb.dbo.#InternalTables') IS NOT NULL
BEGIN
DROP TABLE #InternalTables;
END
CREATE TABLE #InternalTables
(
[internal_type] [TINYINT] NULL
--INDEX CI_InternalType CLUSTERED
, [internal_type_desc] [VARCHAR](60) NULL
, [DBSource] [VARCHAR](16) NULL
)
ON[PRIMARY];
CREATE CLUSTERED INDEX CI_InternalType ON #InternalTables (internal_type);
INSERT INTO #InternalTables
( [internal_type], [internal_type_desc], [DBSource] )
VALUES( 201, N'QUEUE_MESSAGES', N'system database' )
,( 202, N'XML_INDEX_NODES', N'user database' )
,( 203, N'FULLTEXT_CATALOG_FREELIST', N'User Database' )
,( 204, N'FULLTEXT_CATALOG_MAP (BOL)/FULLTEXT_INDEX_MAP (REALITY)',
N'User Database' )
,( 205, N'QUERY_NOTIFICATION', N'User Database' )
,( 206, N'SERVICE_BROKER_MAP', N'system database' )
,( 207, N'EXTENDED_INDEXES', N'user database' )
,( 208, N'FILESTREAM_TOMBSTONE', N'system database' )
,( 209, N'CHANGE_TRACKING', N'User Database' )
,( 210, N'TRACKED_COMMITTED_TRANSACTIONS', N'system database' )
,( 211, N'FULLTEXT_AVDL', N'user database' )
,( 212, N'FULLTEXT_COMP_FRAGMENT', N'user database' )
,( 213, N'FULLTEXT_DOCID_STATUS', N'user database' )
,( 214, N'FULLTEXT_INDEXED_DOCID', N'user database' )
,( 215, N'FULLTEXT_DOCID_FILTER', N'user database' )
,( 216, N'FULLTEXT_DOCID_MAP', N'user database' )
,( 217, N'FULLTEXT_THESAURUS_METADATA_TABLE', N'system database' )
,( 218, N'FULLTEXT_THESAURUS_STATE_TABLE', N'system database' )
,( 219, N'FULLTEXT_THESAURUS_PHRASE_TABLE', N'system database' )
,( 220, N'CONTAINED_FEATURES', N'system database' )
,( 221, N'SEMPLAT_DOCUMENT_INDEX_TABLE', N'user database' )
,( 222, N'SEMPLAT_TAG_INDEX_TABLE', N'user database' )
,( 223, N'SEMPLAT_MODEL_MAPPING_TABLE', N'system database' )
,( 224, N'SEMPLAT_LANGUAGE_MODEL_TABLE', N'system database' )
,( 225, N'FILETABLE_UPDATES', N'system database' )
,( 236, N'SELECTIVE_XML_INDEX_NODE_TABLE', N'user database' )
,( 240, N'QUERY_DISK_STORE_QUERY_TEXT', N'system database' )
,( 241, N'QUERY_DISK_STORE_QUERY', N'system database' )
,( 242, N'QUERY_DISK_STORE_PLAN', N'system database' )
,( 243, N'QUERY_DISK_STORE_RUNTIME_STATS', N'system database' )
,( 244, N'QUERY_DISK_STORE_RUNTIME_STATS_INTERVAL',
N'system database' )
,( 245, N'QUERY_CONTEXT_SETTINGS', N'system database' );
IF OBJECT_ID('tempdb.dbo.#SpaceVersions') IS NOT NULL
BEGIN
DROP TABLE #SpaceVersions;
END
CREATE TABLE #SpaceVersions
(
Product VARCHAR(32)
, ServerMajorVersion DECIMAL(4, 2) --INDEX CI_ServerMajorVer CLUSTERED
, TypesList VARCHAR(256)
);
CREATE CLUSTERED INDEX CI_ServerMajorVer ON#SpaceVersions (ServerMajorVersion);
INSERT INTO#SpaceVersions
( Product
, ServerMajorVersion
, TypesList
)
VALUES
( 'SQL Server 2005', 9.00 , '202,204')
,( 'SQL Server 2008', 10.00 , '202,204,211,212,213,214,215,216')
,( 'SQL Server 2008R2', 10.50 , '202,204,211,212,213,214,215,216')
,( 'SQL Server 2012', 11.00 , '202,204,207,211,212,213,214,215,216,221,222,236')
,( 'SQL Server 2014', 12.00 , '202,204,207,211,212,213,214,215,216,221,222,236')
,( 'SQL Server 2016', 13.00 , '202,204,207,211,212,213,214,215,216,221,222,236');
IF OBJECT_ID('tempdb.dbo.#PreselTypes') IS NOT NULL
BEGIN
DROP TABLE #PreselTypes;
END
SELECT sv.Product, myit.internal_type, myit.internal_type_desc
INTO #PreselTypes
FROM #SpaceVersions sv
CROSS APPLY AdminDB.dbo.stringsplitter(sv.TypesList,',') ss
/* must have DelimitedSplit8K installed http://bit.ly/Moden8KDL */
/* Change database name in accordance with 8k splitter location */
INNER JOIN #InternalTables myit
ON ss.item = myit.internal_type
WHERE sv.ServerMajorVersion = @ServerMajorVersion;
/*
** Summary data.
*/
BEGIN
SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN size ELSE 0 END)) / 128.0
, @logsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 1 THEN size ELSE 0 END)) / 128.0
FROM sys.database_files
END;
WITH FirstPass AS (
SELECT p.object_id, p.partition_id,
ReservedPage = CONVERT(DECIMAL(19,2),SUM(a.total_pages)) / 128.0,
UsedPage = CONVERT(DECIMAL(19,2),SUM(a.used_pages)) / 128.0,
PageCnt = SUM(
CONVERT(DECIMAL(19,2),CASE
WHEN ISNULL(svj.internal_type,1) <> 1 THEN 0
WHEN (a.type <> 1 AND p.index_id < 2)
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 --lob_used_page_count + row_overflow_used_page_count
END
)) / 128.0
,RowCnt = SUM(
CASE
WHEN (p.index_id < 2)
THEN ps.row_count
ELSE 0
END
)
FROM sys.allocation_units a
INNER JOIN sys.partitions p
ON a.container_id = p.partition_id
INNER JOIN sys.dm_db_partition_stats ps
ON p.object_id = ps.object_id
AND p.index_id = ps.index_id
LEFT OUTER JOIN sys.internal_tables it
ON p.object_id = it.object_id
LEFT OUTER JOIN #PreselTypes svj
ON it.internal_type = svj.internal_type
WHERE 1 = 1
AND OBJECTPROPERTY(p.object_id,'IsMSShipped') = ISNULL(@IsMSShipped,OBJECTPROPERTY(p.object_id,'IsMSShipped'))
AND p.index_id IN (0,1,255)
GROUP BY p.object_id, p.partition_id
), FileGroupNames AS (
SELECT (STUFF(
(SELECT DISTINCT ', <' + FILEGROUP_NAME(au.data_space_id) + '>'
FROM sys.allocation_units au
INNER JOIN sys.partitions pu
ON au.container_id = pu.partition_id
WHERE pu.object_id = a.object_id
FOR XML PATH(''), ROOT('FileGroupNames'), TYPE
).value('/FileGroupNames[1]','varchar(max)')
, 1, 2, '')) AS FGList, a.object_id
FROM FirstPass a
),IndexPass AS (
SELECT ps.object_id,
iReservedPage = CONVERT(DECIMAL(19,2),SUM(CASE
WHEN (index_id NOT IN (0,1,255))
THEN (ps.reserved_page_count)
ELSE 0
END)) / 128.0
,iUsedPage = CONVERT(DECIMAL(19,2),SUM(CASE
WHEN (index_id NOT IN (0,1,255))
THEN ps.used_page_count
ELSE 0
END)) / 128.0
,iPageCnt = SUM(
CONVERT(DECIMAL(19,2),CASE
WHEN (index_id NOT IN (0,1,255))
THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
ELSE 0
END
)) / 128.0
,RowCnt = SUM(
CASE
WHEN (index_id < 2)
THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats ps
WHERE 1 = 1
AND OBJECTPROPERTY(ps.object_id,'IsMSShipped') = ISNULL(@IsMSShipped,OBJECTPROPERTY(ps.object_id,'IsMSShipped'))
--AND index_id NOT IN (0,1,255)
GROUP BY ps.object_id
),InternalTables AS (
SELECT ps.object_id,
ReservedPage = CONVERT(DECIMAL(19,2),SUM(reserved_page_count)) / 128.0
,UsedPage = CONVERT(DECIMAL(19,2),SUM(used_page_count)) / 128.0
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it
ON it.object_id = ps.object_id
INNER JOIN #PreselTypes svj2
ON it.internal_type = svj2.internal_type
WHERE it.parent_id = ps.object_id
AND OBJECTPROPERTY(ps.object_id,'IsMSShipped') = ISNULL(@IsMSShipped,OBJECTPROPERTY(ps.object_id,'IsMSShipped'))
GROUP BY ps.object_id
),Summary AS (
SELECT
OBJECT_NAME (F.object_id) AS ObjName
,SCHEMA_NAME(o.schema_id) AS SchemaName
,CASE WHEN OBJECTPROPERTY(F.object_id,'IsMSShipped') = 1
THEN 'YES'
ELSE 'NO'
END AS IsMsShipped
,NumRows = MAX(F.RowCnt)
,ReservedPageMB = SUM(ISNULL(F.ReservedPage,0) + ISNULL(i.ReservedPage,0))
,DataSizeMB = SUM(F.PageCnt)
,IndexSizeMB = SUM(ISNULL(ip.iPageCnt,0))
,UnusedSpace = SUM(CASE WHEN (F.ReservedPage + ISNULL(i.ReservedPage,0)) > (F.UsedPage + ISNULL(i.UsedPage,0))
THEN ((F.ReservedPage + ISNULL(i.ReservedPage,0)) - (F.UsedPage + ISNULL(i.UsedPage,0))) ELSE 0 END)
+ (SUM(ISNULL(ip.iReservedPage,0)) - SUM(ISNULL(ip.iUsedPage,0)))/128
,IndexReservedMB = SUM(ISNULL(ip.iReservedPage,0))
,dbsizeMB = @dbsize
,LogSizeMB = @logsize
,FGList = MAX(fg.FGList)
FROM FirstPass F
INNER JOIN sys.objects o
ON F.object_id = o.object_id
INNER JOIN FileGroupNames fg
ON fg.object_id = F.object_id
LEFT OUTER JOIN InternalTables i
ON i.object_id = F.OBJECT_ID
LEFT OUTER JOIN IndexPass ip
ON F.OBJECT_ID = ip.OBJECT_ID
GROUP BY F.object_id,o.schema_id
),TotalUnallocated AS (
SELECT SUM(ISNULL(UnusedSpace,0)) AS UnusedSpace
,SUM(ISNULL(Summary.ReservedPageMB,0))+SUM(ISNULL(Summary.IndexReservedMB,0))
AS Usedr
FROM Summary
)
SELECT ObjName,SchemaName
,S.FGList
,IsMsShipped,NumRows, ReservedPageMB, ISNULL(DataSizeMB,0) AS DataSizeMB, ISNULL(IndexSizeMB,0) AS IndexSizeMB
,ISNULL(S.UnusedSpace,0) AS UnusedTableSpace, dbsizeMB, LogSizeMB
,TU.UnusedSpace AS Total_AllTables_FreeSpace
,dbsizeMB - TU.Usedr AS DataFileFreeSpace
,PercentofDBPhysFile = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / @dbsize) * 100
,PercentofDBUsedSpace = ((ISNULL(IndexSizeMB,0) + ISNULL(DataSizeMB,0)) / (@dbsize - TU.UnusedSpace)) * 100
INTO #stagetwo
FROM Summary S
CROSS APPLY TotalUnallocated TU
;
SELECT DISTINCT TOP 1 DB_NAME() AS DBName, dbsizeMB, LogSizeMB, Total_AllTables_FreeSpace, DataFileFreeSpace
INTO #dbsizespec
FROM #stagetwo;
ALTER TABLE #stagetwo
DROP COLUMN Total_AllTables_FreeSpace;
ALTER TABLE #stagetwo
DROP COLUMN dbsizeMB;
ALTER TABLE #stagetwo
DROP COLUMN LogSizeMB;
ALTER TABLE #stagetwo
DROP COLUMN DataFileFreeSpace;
IF @TableLastUpdated = 1
BEGIN
ALTER TABLE #stagetwo
ADD TableLastUpdated DATETIME;
UPDATE st
SET TableLastUpdated = (SELECT MAX(last_user_update)
FROM sys.dm_db_index_usage_stats ius
WHERE OBJECT_ID(st.SchemaName + '.' + st.ObjName) = ius.object_id)
FROM #stagetwo st
END
IF @ColumnCount = 1
BEGIN
ALTER TABLE #stagetwo
ADD ColumnCount INT;
UPDATE st
SET ColumnCount = (SELECT COUNT(*)
FROM sys.columns c
WHERE OBJECT_ID(st.SchemaName + '.' + st.ObjName) = c.object_id)
FROM #stagetwo st
END
IF @ProcedureDependencies = 1
BEGIN
ALTER TABLE #stagetwo
ADD ProcedureDependencies VARCHAR(MAX);
UPDATE st
SETProcedureDependencies = STUFF(
(SELECT ', ' + p.name
FROM sys.sql_expression_dependencies d
INNER JOIN sys.procedures p
ON d.referencing_id = p.object_id
WHERE d.referenced_id = OBJECT_ID(st.SchemaName + '.' + st.ObjName)
ORDER BY p.name
FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, SPACE(0)
)
FROM #stagetwo st;
END
SELECT *
FROM #dbsizespec;
SELECT *
FROM #stagetwo st
ORDER BY PercentofDBUsedSpace DESC
DROP TABLE #dbsizespec;
DROP TABLE #stagetwo;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2016 at 9:27 pm
Hello spaghettidba Thank you much for your help
1) I tried it, it gives me null for storeprocedures column and NumberOfRows column for all rows
2) also for lastupdatedate gives user has no rights to do it
is there any possibility can we change this two ? please
Thank you Again
Milan
January 13, 2016 at 9:30 pm
Thank you much for sharing it SQLRNNR
but I don't have rights to execute it but to gain knowledge purpose I should check on it
Thank you Again
January 13, 2016 at 11:11 pm
You will need to discuss with your DBA to ensure you have the appropriate permissions to execute scripts such as this. If the DBA has restricted your access, then you are requesting something for a server that you are not supposed to be able to do.
If you must see the metadata on the server, then you must coordinate with your DBA.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply