Written by 10:30 Database development, Stored Procedures

Stored Procedure to Get Database Tables Information

CodingSight - Stored Procedure to Get Database Tables Information

As SQL Server DBAs, we always take care of one of the most important things for the business, the data. In some cases, applications can get quite complex, and you end up with a ton of database tables scattered around your SQL Server instance(s). This might lead to a few inconveniences, such as:

  • Knowing how your data behave every day, in terms of growth trends (space and/or amount of rows).
  • Knowing what database tables require (or will require) a particular/different strategy to store the data because it is growing too fast.
  • Knowing which of your database tables take too much space, possibly leading to storage constraints.

Due to the importance of these details, I have created a couple of Stored Procedures that can be of great help to any SQL Server DBA that would like to keep track of information regarding database tables in his/her environment. Trust me, one of them is very cool.

Initial Considerations

  • Make sure that the account executing this Stored Procedure has enough privileges. You could probably start with sysadmin and then go as granular as possible to make sure the user has the minimum of privileges required for the SP to work properly.
  • The database objects (database table and stored procedure) will be created inside the database selected at the time the script is executed, so choose carefully.
  • The script is crafted in a way it can be executed several times without getting an error thrown at you. For the Stored Procedure, I used “CREATE OR ALTER PROCEDURE” statement, available since SQL Server 2016 SP1. That’s why don’t be surprised if it doesn’t work smoothly in an earlier version.
  • Feel free to change the names of the created database objects.
  • Pay attention to the parameters of the Stored Procedure that collects the raw data. They can be crucial in a powerful data-collection strategy to visualize trends.

How to Use the Stored Procedures?

  1. Copy & paste the T-SQL Code (available within this article).
  2. The first SP expects 2 parameters:
    1. @persistData: ‘Y’ if a DBA wants to save the output in a target table, and ‘N’ if the DBA wants to see the output directly.
    2. @truncateTable: ‘Y’ to truncate the table first before storing the data captured, and ‘N’ if the current data is kept in the table. Keep in mind that the value of this parameter is irrelevant if the value of the @persistData parameter is ‘N’.
  3. The second SP expects 1 parameter:
    1. @targetParameter: The name of the column to be used to transpose the collected information.

Fields Presented and Their Meaning

  • database_name: the name of the database where the table resides.
  • schema: the name of the schema where the table resides.
  • table_name: the placeholder for the name of the table.
  • row_count: the number of rows that the table currently has.
  • total_space_mb: the number of MegaBytes allocated for the table.
  • used_space_mb: the number of MegaBytes actually in use by the table.
  • unused_space_mb: the number of MegaBytes that the table is not using.
  • created_date: the date/time when the table was created.
  • data_collection_timestamp: visible only if ‘Y’ is passed to the @persistData parameter. It is used to know when the SP was executed and the information was successfully saved in the DBA_Tables table.

Execution Tests

I will demonstrate a few executions of the Stored Procedures:

/* Display the tables information for all user databases */

EXEC GetTablesData @persistData = 'N',@truncateTable = 'N'
Display the tables information for all user databases

/* Persist the information of the database tables and query the target table, truncating the target table first */

EXEC GetTablesData @persistData = 'Y',@truncateTable = 'Y'
SELECT * FROM DBA_Tables
Persist the information of the database tables and query the target table, truncating the target table first

Side Queries

*Query to view the database tables sorted from the largest number of rows to the lowest.

SELECT * FROM DBA_Tables ORDER BY row_count DESC;

*Query to view the database tables sorted from the largest total space to the lowest.

SELECT * FROM DBA_Tables ORDER BY total_space_mb DESC;

*Query to view the database tables sorted from the largest used space to the lowest.

SELECT * FROM DBA_Tables ORDER BY used_space_mb DESC;

*Query to view the database tables sorted from the largest unused space to the lowest.

SELECT * FROM DBA_Tables ORDER BY unused_space_mb DESC;

*Query to view the database tables sorted by creation date, from the newest to the oldest.

SELECT * FROM DBA_Tables ORDER BY created_date DESC;

Here’s a complete code of the Stored Procedure that captures the information of the database tables:

*At the very beginning of the script, you will see the default value that the Stored Procedure assumes if no value is passed for each parameter.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[GetTablesData] 
	@persistData   CHAR(1) = 'Y',
	@truncateTable CHAR(1) = 'Y'
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @command NVARCHAR(MAX)    
	
	DECLARE @Tmp_TablesInformation TABLE(       
	[database]        [VARCHAR](255) NOT NULL,
	[schema]          [VARCHAR](64) NOT NULL,
	[table]           [VARCHAR](255) NOT NULL,
	[row_count]       [BIGINT]NOT NULL,
	[total_space_mb]  [DECIMAL](15,2) NOT NULL,
	[used_space_mb]   [DECIMAL](15,2) NOT NULL,
	[unused_space_mb] [DECIMAL](15,2) NOT NULL,
	[created_date]    [DATETIME] NOT NULL
	)      
	
	SELECT @command = '
	USE [?]
	
	IF DB_ID(''?'') > 4
	BEGIN
		SELECT 
			''?'',
			s.Name AS [schema],
			t.NAME AS [table],
			p.rows AS row_count,
			CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS DECIMAL(15, 2)) AS total_space_mb,
			CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS DECIMAL(15, 2)) AS used_space_mb, 
			CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS DECIMAL(15, 2)) AS unused_space_mb,
			t.create_date as created_date
		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
		LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
		WHERE t.NAME NOT LIKE ''dt%'' 
		  AND t.is_ms_shipped = 0
		  AND i.OBJECT_ID > 255
		GROUP BY t.Name, s.Name, p.Rows,t.create_date
		ORDER BY total_space_mb DESC, t.Name
	END'       
	
	INSERT INTO @Tmp_TablesInformation    
	EXEC sp_MSForEachDB @command      
	   
	IF @persistData = 'N'
		SELECT * FROM @Tmp_TablesInformation 
	ELSE 
	BEGIN
		IF(@truncateTable = 'Y')
		TRUNCATE TABLE DBA_Tables

		INSERT INTO DBA_Tables
		SELECT *,GETDATE() FROM @Tmp_TablesInformation ORDER BY [database],[schema],[table] 
	END
END
GO

Up to this point, the information seems a bit dry, but let me change that perception with the presentation of a complementary Stored Procedure. Its main purpose is to transpose the information collected in the target table that serves as a source for trend reports.

Here’s how you can execute the Stored Procedure:

*For demonstration purposes, I’ve inserted manual records into the target table named t1 to simulate my usual Stored Procedure execution.

*The result set is a bit wide, so I will take a couple of screenshots to show the full output.

EXEC TransposeTablesInformation @targetParmeter = 'row_count' 
manual records inserted into the target table named t1 to simulate usual Stored Procedure execution
manual records inserted into the target table named t1 to simulate usual Stored Procedure execution

Key Takeaways

  • If you automate the execution of the script that populates the target table, you can immediately notice if something went wrong with it or with your data. Take a look at the data for table ‘t1’ and the column ‘15’. You can see NULL there which was done on purpose to show you something that might happen.
  • With this kind of view, you can see a peculiar behavior for the most important/critical database tables.
  • I the given example, I’ve chosen the ‘row_count’ field of the target table, but you can choose any other numeric field as a parameter and get the same table format, but with different data.
  • Don’t worry, if you specify an invalid parameter, the Stored Procedure will warn you and stop its execution.

Here’s a complete code of the Stored Procedure that transposes the information of the target table:

*At the very beginning of the script, you will see the default value that the Stored Procedure assumes if no value is passed for each parameter.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[TransposeTablesInformation] 
	@targetParameter NVARCHAR(15) = 'row_count' 
AS
BEGIN
	SET NOCOUNT ON;

    IF (@targetParameter <> 'row_count' AND @targetParameter <> 'total_space_mb' AND @targetParameter <> 'used_space_mb' AND @targetParameter <> 'unused_space_mb')
	BEGIN
		PRINT 'Please specify a valid parameter!'
		PRINT 'i.e. row_count | total_space_mb | used_space_mb | unused_space_mb'
		RETURN
	END
	ELSE
	BEGIN
		CREATE TABLE #TablesInformation(
			[database] [VARCHAR](255) NOT NULL,
			[schema]   [VARCHAR](64) NOT NULL,
			[table]    [VARCHAR](255) NOT NULL,
			[1]		   [DECIMAL](10,2) NULL,
			[2]		   [DECIMAL](10,2) NULL,
			[3]		   [DECIMAL](10,2) NULL,
			[4]		   [DECIMAL](10,2) NULL,
			[5]		   [DECIMAL](10,2) NULL,
			[6]		   [DECIMAL](10,2) NULL,
			[7]		   [DECIMAL](10,2) NULL,
			[8]		   [DECIMAL](10,2) NULL,
			[9]		   [DECIMAL](10,2) NULL,
			[10]	   [DECIMAL](10,2) NULL,
			[11]	   [DECIMAL](10,2) NULL,
			[12]	   [DECIMAL](10,2) NULL,
			[13]	   [DECIMAL](10,2) NULL,
			[14]	   [DECIMAL](10,2) NULL,
			[15]	   [DECIMAL](10,2) NULL,
			[16]	   [DECIMAL](10,2) NULL,
			[17]	   [DECIMAL](10,2) NULL,
			[18]	   [DECIMAL](10,2) NULL,
			[19]	   [DECIMAL](10,2) NULL,
			[20]	   [DECIMAL](10,2) NULL,
			[21]	   [DECIMAL](10,2) NULL,
			[22]	   [DECIMAL](10,2) NULL,
			[23]	   [DECIMAL](10,2) NULL,
			[24]	   [DECIMAL](10,2) NULL,
			[25]	   [DECIMAL](10,2) NULL,
			[26]	   [DECIMAL](10,2) NULL,
			[27]	   [DECIMAL](10,2) NULL,
			[28]	   [DECIMAL](10,2) NULL,
			[29]	   [DECIMAL](10,2) NULL,
			[30]	   [DECIMAL](10,2) NULL,
			[31]	   [DECIMAL](10,2) NULL
		)

		INSERT INTO #TablesInformation([database],[schema],[table])
		SELECT DISTINCT [database_name],[schema],[table_name]
		FROM DBA_Tables
		ORDER BY [database_name],[schema],table_name

		DECLARE @databaseName  NVARCHAR(255)
		DECLARE @schemaName    NVARCHAR(64)
		DECLARE @tableName     NVARCHAR(255)
		DECLARE @value	       DECIMAL(10,2)
		DECLARE @dataTimestamp DATETIME
		DECLARE @sqlCommand    NVARCHAR(MAX)

		IF(@targetParameter = 'row_count')
		BEGIN
			DECLARE TablesCursor CURSOR FOR
			SELECT 
					[database_name],
					[schema],
					[table_name],
					[row_count],
					[data_collection_timestamp]
			FROM DBA_Tables
			ORDER BY [database_name],[schema],table_name
		END

		IF(@targetParameter = 'total_space_mb')
		BEGIN
			DECLARE TablesCursor CURSOR FOR
			SELECT 
					[database_name],
					[schema],
					[table_name],
					[total_space_mb],
					[data_collection_timestamp]
			FROM DBA_Tables
			ORDER BY [database_name],[schema],table_name
		END

		IF(@targetParameter = 'used_space_mb')
		BEGIN
			DECLARE TablesCursor CURSOR FOR
			SELECT 
					[database_name],
					[schema],
					[table_name],
					[used_space_mb],
					[data_collection_timestamp]
			FROM DBA_Tables
			ORDER BY [database_name],[schema],table_name
		END

		IF(@targetParameter = 'unused_space_mb')
		BEGIN
			DECLARE TablesCursor CURSOR FOR
			SELECT 
					[database_name],
					[schema],
					[table_name],
					[unused_space_mb],
					[data_collection_timestamp]
			FROM DBA_Tables
			ORDER BY [database_name],[schema],table_name
		END

		OPEN TablesCursor

		FETCH NEXT FROM TablesCursor INTO @databaseName,@schemaName,@tableName,@value,@dataTimestamp

		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			SET @sqlCommand = CONCAT('
			UPDATE #TablesInformation
			SET [',DAY(@dataTimestamp),'] = ',@value,'
			WHERE [database] = ',CHAR(39),@databaseName,CHAR(39),'
			  AND [schema] = ',CHAR(39),@schemaName+CHAR(39),'
			  AND [table] = ',CHAR(39),@tableName+CHAR(39),'
			')
			EXEC(@sqlCommand)

			FETCH NEXT FROM TablesCursor INTO @databaseName,@schemaName,@tableName,@value,@dataTimestamp
		END

		CLOSE TablesCursor

		DEALLOCATE TablesCursor

		IF(@targetParameter = 'row_count')
		SELECT [database],
			   [schema],
			   [table],
			   CONVERT(INT,[1])  AS [1],
			   CONVERT(INT,[2])  AS [2],
			   CONVERT(INT,[3])  AS [3],
			   CONVERT(INT,[4])  AS [4],
			   CONVERT(INT,[5])  AS [5],
			   CONVERT(INT,[6])  AS [6],
			   CONVERT(INT,[7])  AS [7],
			   CONVERT(INT,[8])  AS [8],
			   CONVERT(INT,[9])  AS [9],
			   CONVERT(INT,[10]) AS [10],
			   CONVERT(INT,[11]) AS [11],
			   CONVERT(INT,[12]) AS [12],
			   CONVERT(INT,[13]) AS [13],
			   CONVERT(INT,[14]) AS [14],
			   CONVERT(INT,[15]) AS [15],
			   CONVERT(INT,[16]) AS [16],
			   CONVERT(INT,[17]) AS [17],
			   CONVERT(INT,[18]) AS [18],
			   CONVERT(INT,[19]) AS [19],
			   CONVERT(INT,[20]) AS [20],
			   CONVERT(INT,[21]) AS [21],
			   CONVERT(INT,[22]) AS [22],
			   CONVERT(INT,[23]) AS [23],
			   CONVERT(INT,[24]) AS [24],
			   CONVERT(INT,[25]) AS [25],
			   CONVERT(INT,[26]) AS [26],
			   CONVERT(INT,[27]) AS [27],
			   CONVERT(INT,[28]) AS [28],
			   CONVERT(INT,[29]) AS [29],
			   CONVERT(INT,[30]) AS [30],
			   CONVERT(INT,[31]) AS [31]
		FROM #TablesInformation
		ELSE
		SELECT * FROM #TablesInformation
	END
END
GO

Conclusion

  • You can deploy the data collection SP in every SQL Server instance under your support and implement an alerting mechanism across your entire stack of supported instances.
  • If you implement an agent job that queries this information relatively frequently, you can stay on top of the game in terms of knowing how your data behaves during the month. Of course, you can go even further and store the monthly collected data to have an even bigger picture; you’d have to make some tweaks to the code, but it would be totally worth it.
  • Make sure to test this mechanism properly in a sandbox environment and, when you are planning for a production deployment, make sure to choose low activity periods.
  • Collecting information of this type can help differentiate a DBA from one another. There are probably 3rd party tools that can do the same thing, and even more, but not everyone has the budget to afford it. I hope this can help anyone who decides to use it in their environment.
(Visited 31 times, 3 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close