SQL SERVER: Display Row count of all the tables

Recently we have performance issue in SQL SERVER and we decided to move some large tables to different file group, so we can improve SQL SERVER performance, as it uses other disks I/O. Now challenge is to identify large tables from many tables (approx 700 tables).

There are few ways to display row count of all the tables:

1. Use Procedure: sp_msForEachTable (Simplest way)


2. Read information from system tables ( approximate count, Preffered)

Let me explain both of the method:

1. Use Procedure: sp_msForEachTable:

This is the simplest way that we can make COUNT(*) to each table to find number of rows from each table. This is undocumented stored procedure,"sp_msForEachTable" gives ability to make a query to all tables.

EXEC sp_msForEachTable 'SELECT ''?'' AS TableName,COUNT(*) AS NoOfRows FROM ?'

Its nothing but just a simple dynamic SQL statement where "?" will be replaced by table name. So query will be like:

SELECT COUNT(*) FROM BuildVersion

This procedure, "sp_msForEachTable", will loop to each table for current database and count rows from each table. So it gives result like:



By this way, we can get row count from all the tables for current database. But question is: How to use this result set? How to find out which table has maximum rows? To find out this, I need to check each table's rows manually and then only I can find out which table has maximum rows. This is time consuming and not the good way to find out table with maximum rows. Let see another method on which we can find it out it easily.

2. Read information from system tables:

By reading system tables, we can also find out rows from each table. We can use following query to find out number of rows from each table.

SELECT 
 st.Name AS TableName,
 SUM(  
  CASE  
   WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
   ELSE 0  
  END  
 ) AS NoOfRows
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name

NOTE: This method is used to get an *approximate* count for all tables.

This query solved my purpose. In this query, it will give result in single result set, so we can manipulate it easily.



E.g. List out all tables and sort it out based on the NumberOfRows.
So, my query will be:

SELECT 
 st.Name AS TableName,
 SUM(  
  CASE  
   WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows  
   ELSE 0  
  END  
 ) AS NoOfRows
FROM sys.partitions p
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN sys.tables st ON st.object_id = p.Object_ID
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
GROUP BY st.name
ORDER BY NoOfRows DESC

Let me know your opinions.

SQL SERVER Session at Dotnetchaps - Developer Conference 2010

Dotnetchaps is a group of people focused on Microsoft technologies .Net and SQL Server. The User Group organizes technical sessions for sharing knowledge on .Net and SQL Server. The motto of this group is to Learn, Share and Grow.

DotNetChaps user group is also associated with www.sqlpass.org community and officially opened the SqlPass chapter at Anand. The chapter name is Anand India Sql Server Users group.

Today, I am presenting a SQL SERVER session in this User Group meeting organized at SURAT, GUJARAT.

My session is at 2:10 PM which consists of following:

1. What's new in SQL SERVER 2008 R2?

2. Demo of SSRS Reports

In my SQL SERVER session, I am going to explain Utility Control Point (UCP), Data-Tier Application (DAC), Unicode compression. I am going to explain this feature by making examples on SQL SERVER R2, so people have better idea about it.

For SQL SERVER Reporting Services, its very simple to Design, develop and deploy report to report Server. It is very simple to integrate deployed report to web application.

Please find registration and venue details at: http://www.dotnetchaps.com/Blogs/Dotnetchaps-Blog/June-2010/Dotnetchaps---Developer-Conference-2010.aspx

SQL SERVER: Find executing queries

Recently my company owner reported that SQL SERVER is running slow. I searched for it and get following T-SQL query. SQL SERVER provides dynamic views, which contains current information about all details.

 

SELECT OBJECT_NAME(ObjectID) as ObjectName,
  st.Text, 
  DB_NAME(database_ID) as dbname,
  Blocking_session_ID as BlockingSessionID,  
  *
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

 

It will give us details like about running SQL Objects, with Database name:

 

 

Reference : Tejas Shah http://www.SQLYoga.com

My First article on SQL SERVER Central about Access variables values from Trigger

I am glad to inform you all that, my article on "Accessing variable from Trigger", which I wrote a few days back, have been published by "SQL SERVER CENTRAL" site.

I would recommend all SQL lovers, to subscribe to the news letter of SQL SERVER CENTRAL, which consists of tips and tricks, useful in real world applications.

Please feel free to contact me at tejasnshah.it@gmail.com for any MS SQL SERVER query/help.

PS: Hope you all will read more and more articles from me, in forthcoming newsletters ;)

Enjoy Reading!!

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder. There is a requirement to save File name along with record, so later on we can identify which record comes from which file. Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily. 1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task. 2. Right click on Flat File Source, and click on Show Advanced Editor: 3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:   4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

Let me know your comments/questions.

 

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.

SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.

Let me show you how to use it. Syntax to use SP:

EXEC SP_PROCOPTION     
@ProcName = 'SPNAME',    
@OptionName = 'startup',    
@OptionValue = 'true/false OR on/off'

• @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
  
• @OptionName, should be "startup" always.
  
• @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.

That's it, I hope this is very clear to use this feature.

Reference : Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: SSIS - Derived Column Data Flow Transformation

As I explained earlier about Foreach Loop Container. One of regular reader of blog send me an email about one issue. Let me share that problem with all readers. With this example, Foreach Loop Container, What to do if we want to save file name along with each row, so we can come to know that which row is from which file ? This is very practical problem that we need to fix. To solve this, I come up with following solution. 1. I used "Derived Column", one of Data Flow Transformations in Data Flow Operations.    2. Configure Derived Column:   As we have variable, FileName, as defined in, SQL SERVER: SSIS - Foreach Loop Container. Here I used that variable as a new column. By dragging  that User variable to Expression. By default it assign UNICODE STRING DataType to this new column. We need to change it by: A. Right click on "Derived Column", Go to Show Advanced Editor B. Set DataType to String as: 3. That's it. Now just add it to Destination Column Mapping with your Database column. Let me know your suggestions.