Get SQL Table Information

• Download script - 1.41 KB

Introduction

One of the common requirements is to view the Table information of SQL server database. Here is the easiest way to view the SQL Table Information.

Background

Couple of days ago, one of my friends asked me why we always use the ADO.NET class library to get any SQL Table information runtime! SQL Server provides several "standard" techniques for it. So I decided to try it and I found it’s really cool and I believe that it is always better to use the standard techniques provided by SQL Server where possible.

This is easy enough to do with cursors and dynamic SQL, but after looking at how “sp_ spaceused” worked and how SMO gets the same information, I decided that I could write it without either.

Even better, once I wrote it through, I realized that it could easily be rewritten as a StoredProcedure. Now I could reuse it by joining it with other tables and views in new queries or procedures whenever I wanted.

Using the Code

I exercise StoredProcedure “sp_spaceused” which displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or service broker queue in the current database, or displays the disk space reserved and used by the whole database. In other words, this StoredProcedure computes the amount of disk space used for data and indexes, and the disk space used by a table in the current database. A sample code is given below:

/****** Object:  StoredProcedure [dbo].[spTableInformation]    
Script Date: 03/05/2009 21:33:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Md. Marufuzzaman>
-- Create date: <Create Date,,03/05/2009>
-- Description:    <Description,,>
--Thanks to ,, RBarry Young
-- =============================================
CREATE PROCEDURE [dbo].[spTableInformation]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

SELECT     SCHEMA_NAME(SYSTBL.SCHEMA_ID) AS [SCHEMA], SYSTBL.NAME, COALESCE
                          ((SELECT     NAME
                              FROM         sys.database_principals AS SDBP
                              WHERE     (PRINCIPAL_ID = SYSTBL.PRINCIPAL_ID)), _
				SCHEMA_NAME(SYSTBL.SCHEMA_ID)) AS OWNER, _
				SYSTBL.MAX_COLUMN_ID_USED AS COLUMNS, 
                      CAST(CASE SINDX_1.INDEX_ID WHEN 1 THEN 1 ELSE 0 END AS BIT) _
				AS HASCLUSIDX, COALESCE
                          ((SELECT     SUM(rows) 
                              FROM         sys.partitions AS SPART
                              WHERE     (OBJECT_ID = SYSTBL.OBJECT_ID) AND _
				(INDEX_ID < 2)), 0) AS [ROWCOUNT], COALESCE
                          ((SELECT     CAST(SPTV.low / 1024.0 AS FLOAT) 
                                                    * SUM(SAU_1.USED_PAGES - _
						CASE WHEN SAU_1.TYPE <> 1 _
						THEN SAU_1.USED_PAGES WHEN _
						SYSP.INDEX_ID < 2 THEN _
						SAU_1.DATA_PAGES
                                                     ELSE 0 END) 
                              FROM         sys.indexes AS SINDX_2 INNER JOIN
                                                    sys.partitions AS SYSP ON _
						SYSP.OBJECT_ID = _
						SINDX_2.OBJECT_ID _
						AND SYSP.INDEX_ID = _
						SINDX_2.INDEX_ID INNER JOIN
                                                    sys.allocation_units AS SAU_1 ON _
						SAU_1.CONTAINER_ID = _
						SYSP.PARTITION_ID
                              WHERE     (SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID)), 0.0) _
						AS INDEXKB, COALESCE
                          ((SELECT     CAST(SPTV.low / 1024.0 AS FLOAT) 
                                                    * SUM(CASE WHEN SAU_2.TYPE <> 1 _
						THEN SAU_2.USED_PAGES _
						WHEN SYSP.INDEX_ID < 2 _
						THEN SAU_2.DATA_PAGES ELSE 0 END) 
                                                    AS Expr1
                              FROM         sys.indexes AS SINDX_2 INNER JOIN
                                                    sys.partitions AS SYSP ON _
						SYSP.OBJECT_ID = _
						SINDX_2.OBJECT_ID _
						AND SYSP.INDEX_ID = _
						SINDX_2.INDEX_ID INNER JOIN
                                                    sys.allocation_units AS SAU_2 _
						ON SAU_2.CONTAINER_ID = _
						SYSP.PARTITION_ID
                              WHERE     (SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID)), 0.0) _
				AS DATAKB, SYSTBL.CREATE_DATE, SYSTBL.MODIFY_DATE
FROM         sys.tables AS SYSTBL INNER JOIN
                      sys.indexes AS SINDX_1 ON SINDX_1.OBJECT_ID = SYSTBL.OBJECT_ID _
						AND SINDX_1.INDEX_ID < 2 INNER JOIN
                      master.dbo.spt_values AS SPTV ON SPTV.NUMBER = 1 AND SPTV.type = 'E'
                      
END

GO

Conclusion

I hope that this article might be helpful to you. Enjoy!

History

• 19^th July, 2009: Initial post