Archive for the ‘Tables Specifications’ Category

How to find the most recent updates for a table?

You wish to find the most recent updates for a table or for all tables in a SQL Server database, you wanted to know if the data are being updated at all. How can you do it as quickly as possible? Yes. There’s a Dynamic Management View for that and it is: sys.dm_db_index_usage_stats.

 

Here’s an example of how you can use it and verify it:

  1. Create a test table.
  2. Insert some data into the test table.
  3. Query the date and time the data is inserted.
  4. Query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table.
  5. Update the table by modifying data.
  6. Query the date and time the data is updated.
  7. Query the sys.dm_db_index_usage_stats DMV to confirm the most recent update to the table.

Example Code:

USE AdventureWorks2012
GO
-- create a test table
CREATE TABLE VeryTalentedPeople
(
ID INT identity (1,1)
, FIRSTNAME VARCHAR(100)
, LASTNAME VARCHAR(100)
)
GO

-- insert some data into the table
INSERT INTO VeryTalentedPeople
VALUES ('Barak','Obama')
, ('Ellie', 'Goulding')
, ('Katy', 'Perry')
, ('Lady','Gaga')
GO

-- query the date and time the data is inserted
SELECT GETDATE()

-- query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks2012')
AND OBJECT_ID=OBJECT_ID('VeryTalentedPeople')

-- update the table by modifying data
UPDATE VeryTalentedPeople
SET FIRSTNAME = 'Stefani'
, LASTNAME = 'Germanotta'
WHERE FIRSTNAME = 'Lady'
and LASTNAME = 'Gaga'

-- query the date and time the data is updated
SELECT GETDATE()

-- query the sys.dm_db_index_usage_stats DMV to see the most recent update to the table
SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'AdventureWorks2012')
AND OBJECT_ID=OBJECT_ID('VeryTalentedPeople')

REFERENCES:

sys.dm_db_index_usage_stats
http://msdn.microsoft.com/en-us/library/ms188755.aspx

How to query each of the table rowcount, reserved space in MB, data space in MB, index size in MB and unused space in MB?

You wanted to find out the row count, space used in MB, index size in MB, and unused space in MB for all tables in the database. With the undocumented stored proc sp_MSforeachtable combining with the sp_spaceused stored proc, now you can and here’s an example of how it can be done:


-- Querying the table rowcount, reserved space in MB, data space in MB, index size in MB, and unused space in MB

-- create a temp table to store the tables spaces info for all tables for all databases in the instance
IF OBJECT_ID('TEMPDB..#TableSpecs') IS NOT NULL
DROP TABLE #TableSpecs

CREATE TABLE #TableSpecs
(
 ID int identity(1,1)
 ,Table_Name VARCHAR(123)
 ,Table_Rowcnt INT
 ,ReservedSpaceKB VARCHAR(25)
 ,SpaceUsedKB VARCHAR(25)
 ,Index_SizeKB VARCHAR(25)
 ,UnusedSpaceKB VARCHAR(25)
)

-- use the sp_msforeachtable stored proc to load the table specs info
INSERT INTO #TableSpecs
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

-- convert spaces from KB to MB
SELECT ts.Table_Name
 ,Table_Rowcnt
 ,ROUND(CAST(REPLACE(ReservedSpaceKB, 'KB', '') AS FLOAT) / 1024,2) AS ReservedSpaceMB
 ,ROUND(CAST(REPLACE(SpaceUsedKB, 'KB', '') AS FLOAT) / 1204,2) AS SpaceUsedMB
 ,ROUND(CAST(REPLACE(Index_SizeKB, 'KB', '') AS FLOAT) / 1024,2) AS IndexSizeMB
 ,ROUND(CAST(REPLACE(UnusedSpaceKB, 'KB', '') AS FLOAT) / 1024,2) AS UnusedSpaceMB
FROM #TableSpecs ts
ORDER BY SpaceUsedMB desc, Table_Rowcnt desc
-- you can change the order by columns as per your desire

References:

sp_spaceused
http://msdn.microsoft.com/en-us/library/ms188776(v=sql.105).aspx