Click here to Skip to main content
12,505,902 members (47,519 online)
Click here to Skip to main content
Add your own
alternative version

Stats

19.9K views
190 downloads
38 bookmarked
Posted

Get SQL Table Information

, 19 Jul 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Get SQL Table Information

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.

TableInfo.JPG

Output figure

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

  • 19th July, 2009: Initial post 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Md. Marufuzzaman
Architect
Bangladesh Bangladesh
A highly experienced leader with successful track record of software development, product innovations, brand management and corporate communication etc. Some successful product innovations have also achieved and awards “Most Valuable Professional” (MVP) at 2010 and 2011 by codeproject.com and also selected as a mentor of codeproject.com. Published over 100 technical articles in various software development resource sites (i.e., codeprojetc.com, Microsoft MSDN, and IEEE & IBM (In progress)) and various IT Forums, Blogs etc.

Over ten years of professional experiences in ICT field having extensive experience in formulating corporate vision and long term strategy. Leading development related functions including design, development, services, data management and analytics, customer experience management, content services, digital analytics and optimization.

An individual with results-driven approach and relentless in pursuit of excellence from a business and organizational standpoint. Believes in transparency, commitment and teamwork.

Expertise: Software/Solution Architect, SaaS platform base application, Large scale win32/web based business software solutions, enterprise applications, integration, etc.

Technologies/Tools: Microsoft.Net version 05/08/10/12, Microsoft SQL Server version 7/2K/05/08/12 , Oracle version 10/11, MySql version 5.1, 5.5, PS2, Visual C#, R, VB.NET, ASP.NET, PHP, API, MVC, WebAPI , Add-In Visual Basic etc.,. I have also more than two years’ of strong experience in mobile-VAS (platform development).

Points of Interest: Technology and research & development especially focused on business functionalities and social business areas as well, few stuff including:

1.R&D on new techniques as required to increase business revenue.
2.Urban and rural sectors to improve people’s lifestyle, better medical facilities, education, social business etc., using innovative technical solutions.
3.Research and innovative product development.
4.Etc.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 5 Pin
Daniel Miller22-Dec-15 11:35
professionalDaniel Miller22-Dec-15 11:35 
GeneralRe: My vote of 5 Pin
Md. Marufuzzaman23-Dec-15 1:14
mentorMd. Marufuzzaman23-Dec-15 1:14 
GeneralMy vote of 5 Pin
Saeid.Babaei8621-Feb-12 20:38
memberSaeid.Babaei8621-Feb-12 20:38 
GeneralMy vote of 2 Pin
jpaulino3-Aug-09 9:14
memberjpaulino3-Aug-09 9:14 
GeneralRe: My vote of 2 Pin
Md. Marufuzzaman5-Oct-11 23:05
mvpMd. Marufuzzaman5-Oct-11 23:05 
GeneralMy vote of 2 Pin
VMykyt19-Jul-09 11:25
memberVMykyt19-Jul-09 11:25 
GeneralRe: My vote of 2 Pin
Md. Marufuzzaman26-Jul-09 5:10
groupMd. Marufuzzaman26-Jul-09 5:10 
GeneralMy vote of 2 Pin
Sacha Barber19-Jul-09 8:46
mvpSacha Barber19-Jul-09 8:46 
GeneralRe: My vote of 2 Pin
Md. Marufuzzaman26-Jul-09 5:11
groupMd. Marufuzzaman26-Jul-09 5:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160927.1 | Last Updated 19 Jul 2009
Article Copyright 2009 by Md. Marufuzzaman
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid