How to Get Tables Information in SQL Server via Stored Procedure

As a database administrator, developer, or data analyst, understanding the structure and details of your SQL Server database tables is crucial. It aids in optimizing performance, debugging issues, and making informed decisions about data management.

In this post, we will delve into the practical steps of using stored procedures to retrieve tables information in SQL Server. Stored procedures, precompiled collections of SQL statements, are a powerful tool that can make your database interactions more efficient and secure. They can be used to perform a variety of tasks, including retrieving information about the tables in your database.

Whether you’re new to SQL Server or an experienced professional looking to refine your skills, this guide will provide you with a clear, step-by-step process to access valuable table information. We’ll cover everything from the basics of stored procedures to the specific code needed to get the job done.

So, let’s embark on this journey to unlock the potential of stored procedures in SQL Server and make the most out of your database interactions.

Why Getting Tables Information in SQL Server is Crucial

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.

Starting Points to Retrieve Database Tables Information

• 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 Employ Stored Procedures for Fetching Database Tables Details?

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.

Decoding Fields in the Process of Extracting Database Tables Information

• 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.

Code Example for Getting Database Tables Information

I will demonstrate a few executions of the Stored Procedures:

/* Display the tables information for all user databases

EXEC GetTablesData @persistData = 'N',@truncateTable = 'N'

/* 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

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;

Stored Procedure Code for Fetching Database Tables Details

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' 

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 3^rd 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.

Others Stored Procedures for SQL Server

Stored Procedure to Get Logins and Server Roles Inventory
Stored Procedure to Get Database Tables Information
How to Show List of Indexes in SQL Server using Stored Procedure
How to Use the sp_whoisactive PowerShell Script for Real-Time SQL Server Monitoring
How to Get SQL Server Statistics Information Using System Statistical Functions
SQL Server RAISERROR Statement with Simple Examples
Stored Procedure to Delete Duplicate Records in SQL Table
How to Get Backup Status in SQL Server using Stored Procedure