SQL Server Row Count for all Tables in a Database

Getting the row count from each table one by one and comparing and consolidating the results can be a tedious task. Hence any script/solution which can get the row count information from all the tables in a database can be really helpful and effective thereby considerably reducing the effort involved. In this tip we will see four different approaches to get the row counts from all the tables in a SQL Server database.

Let's take a look at each of the approaches:

• sys.partitions Catalog View
• sys.dm_db_partition_stats Dynamic Management View (DMV)
• sp_MSforeachtable System Stored Procedure
•  COALESCE() Function

Approach 1: sys.partitions Catalog View

sys.partitions is an Object Catalog View and contains one row for each partition of each of the tables and most types of indexes (Except Fulltext, Spatial, and XML indexes). Every table in SQL Server contains at least one partition (default partition) even if the table is not explicitly partitioned.

The T-SQL query below uses the sys.partitions Catalog View to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

1.  Requires membership in the public role.
2. Can be used even when working with source systems which offer limited privileges such as read-only. 

Approach 2: sys.dm_db_partition_stats Dynamic Management View (DMV)

sys.dm_db_partition_stats is a Dynamic Management View (DMV) which contains one row per partition and displays the information about the space used to store and manage different data allocation unit types - IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA.

The T-SQL query below uses the sys.dm_db_partition_stats DMV to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

1. VIEW DATABASE STATE permissions are required in the database.
2. The values in the sys.dm_db_partition_stats DMV are reset on server restart or when an object/partition is dropped and recreated.

In general, querying the Dynamic Management Views (DMVs), requires VIEW SERVER STATE or VIEW DATABASE STATE permissions based on the Dynamic Management View/Function which is being queried.

---

Approach 3: sp_MSforeachtable System Stored Procedure

sp_MSforeachtable is an undocumented system stored procedure which can be used to iterate through each of the tables in a database. In this approach we will get the row counts from each of the tables in a given database in an iterative fashion and display the record counts for all the tables at once.

The T-SQL query below uses the sp_MSforeachtable system stored procedure to iterate through each of the tables to capture the row count for all the tables in a database.

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

Below are the highlights of this approach:

1. This is an iterative approach which captures the row count for each of the individual tables, puts them together and displays the results for all the tables.
2. sp_MSforeachtable is an undocumented system stored procedure.
3. This approach can be used for testing purposes but it is not recommended for use in any production code. sp_MSforeachtable is an undocumented system stored procedure and may change anytime without prior notification from Microsoft.

---

Approach 4: COALESCE() Function

The COALESCE() function is used to return the first non-NULL value/expression among its arguments. In this approach we will build a query to get the row count from each of the individual tables with UNION ALL to combine the results and run the entire query.

The T-SQL query below uses the COALESCE() function to iterate through each of the tables to dynamically build a query to capture the row count from each of the tables (individual COUNT queries combined using UNION ALL) and provides the row counts for all the tables in a database.

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

Below are the highlights of this approach:

1. Can be used in cases where the number of tables is huge like say few hundred tables. This query can be modified to capture the row counts from a set of tables at one time instead of all the tables which might otherwise put a lot of load on the system.
2. Can be used even when working with source systems which offer limited privileges such as read-only.

---

Below is sample output from AdventureWorksDW database. Note - the results in your AdventureWorksDW database might vary slightly from what is displayed below due to various reasons like the version of database being used, any changes/data manipulation done on the database for testing purposes, etc.

Next Steps

• Review the scripts/approaches in this tips to see which approach suits the best for your scenario.
• Check out these additional resources:
  • Catalog Views
  • Dynamic Management Views and Functions

SQL Server stored procedure to generate random passwords

The following stored procedure creates strings of random characters based on four parameters that configure the result.

1. LEN - specifies the length of the result (required)
2. MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
3. RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74"  (48 + 74 = 122) where 122 is a lowercase "z")
4. EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)

Here is the stored procedure:

create proc [dbo].uspRandChars
    @len int,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go 

To use the stored procedure issue commands such as the following. 

declare @newpwd varchar(20)
-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd
-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd
-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd
-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd 

Here is sample output from the above commands: 

Next Steps

• Unsure what ASCII codes to use, refer to this list of ASCII codes
• See where this procedure can be used in your application. Some examples include users inviting their friends or generating a new password by request or as a temporary replacement.
• Determine your environment's security policy including password length and valid characters.
• Implement your password policy in the parameters.

Scripting triggers in Microsoft SQL Server

By default when you use the Script Wizard in Microsoft SQL Server Management Studio to script objects in a database, triggers are note included.

You can include triggers if you [Include all objects] and [Set triggers= true] during the Script Wizard.

To start the script wizard, right click the database that contains the triggers.

Choose Tasks > Generate Scripts…

Check the [Script all objects in the selected database]

Set the [Script Triggers = True]

The generate script will now contain all triggers in the database.

It will not generate drop statements for the triggers

So you should at [drop trigger] if it exists, before executing the generated create trigger statements:

For example the script wizard generates:

CREATE TRIGGER [trd_Order] on [Order]
AFTER DELETE
AS

    INSERT Audit_Order
    SELECT *, 'V',0,getdate()
    ,    null
    ,    null
     FROM deleted
END
GO

Then you should change this to:

IF OBJECT_ID ('trd_Order', 'TR') IS NOT NULL
BEGIN
   DROP TRIGGER trd_Order
END

CREATE TRIGGER [trd_Order] on [Order]
AFTER DELETE
AS

    INSERT Audit_Order
    SELECT *, 'V',0,getdate()
    ,    null
    ,    null
     FROM deleted
END
GO

Debugging Stored Procedures in Visual Studio 2005

The first thing you want to do is add a data connection in the Visual Studio 2005 Servers windows. Right click on "Data Connections" and click on "Add New Connection":

Enter the required connection info in the "Add Connection" dialog and click "Test Connection" to test the connection. If that succeeds, click OK:

After you add the data connection you will be able to see the connection in the Data Connections tree:

We're going to debug "usp_contact_add" in this example (any proc will do, it really doesn't matter what proc YOU use). Drill down until you can see the proc you're debugging under the "Stored Procedures" folder:

Right click on the proc and click "Open". You will see the proc code in the code window:

"Now we debug"

Now that we've figured out how to open the proc we're going to get into actually debugging it. Right click on the proc and click "Step Into Stored Procedure":

A window with all of the parameters for the proc will pop up:

Notice it says "The stored procedure ... REQUIRES the following parameters:" That means you are REQUIRED to fill in the values. Otherwise the debugger will quit right away. That said; click the OK button to step into the procedure. The debugger will automatically stop execution on the first line of code in the stored procedure. When the debugger has stopped execution you'll see a yellow arrow to the left of the line of code:

Depending on how you have your Visual Studio settings you will click either F8 (VB) or F11(C#) to go to the next line of code. The debugger will execute the previous line of code and move to the next line:

Now "nocount" is "set" to "on". Click F8 or F11 again and the insert statement (or whatever line of code the yellow arrow is pointing) will be executed.

Ok... So can we doing anything except watch the debugger work?

Yes, in fact there are several things you can do but I'm going to concentrate on what I use most in VS2005 while debugging stored procedures.

While you're debugging you can view / edit the value of the variables in scope using a few different methods. The first way to view/edit a variable is simply hovering your mouse over the variable:

The next way to view/edit the variable is using watches. The first type of watch is the "Quick Watch". A quick watch simply shows you the value in its current state. Right click on the variable and click "Quick Watch":

The quick watch window will be displayed:

The second type of watch is the "old school" watch using the "Add Watch" option. Right click on the variable and click "Add Watch". Clicking "Add Watch" will add the variable to the watch window. If you don't see the watch window press CTRL + D, W to bring it into focus.

The third way of viewing / editing the variables is the locals window...my favorite! (CTRL + D, L). This window will list all of the variables and their associated values:

If you're feeling adventurous you might give the immediate window (CTRL + D, I) a try.

I love breakpoints, you love breakpoints, If we didn't use breakpoints, we'd be lame.

(Note: The above title is set to the "Cheesy Poofs" theme song from South Park episode 211)

Another cool feature while debugging procedures is breakpoints. If you don't want to deal with stepping through each line of code to get to the problem code you can add a breakpoint. A breakpoint will stop execution at the line of code the breakpoint is set on while the debugger is active. This is invaluable when debugging a really large procedure or the last DBA's code. We all know the last DBA didn't know what they were doing!

To add a breakpoint click on the line of code where you want the breakpoint and press the F9 key. After you press F9 you should see a red "ball" to the left of code where the breakpoint was set:

Now we want to execute the procedure and hit our breakpoint. Right click the code window and click "Step Into Stored Procedure". Again, fill in the data in the parameters window and click OK. The debugger will again stop on the first line of code. Click F5 to tell the debugger to start running again. The debugger will now stop execution at the breakpoint:

There you go. Getting through all of the code you don't want to execute manually is as easy as pressing F9 (<< That sets the breakpoint).

(Note: Pressing F5 while the debugger is active will execute the stored proc until it: 1) hits a breakpoint. 2) Exits the proc.)

Get return value from stored procedure

This article discusses three common ways to return data from stored procedures: returning result sets (SELECT statements), using output variables and using the RETURN statement.  Each example includes client-side code (ASP.NET) and server-side code (T-SQL) to read the results.

Result Sets

Result sets are what you get when you run a simple SELECT statement inside a stored procedure. Let's suppose you want a stored procedure to return a list of all the people with a given last name.  The code for the stored procedure might look like this:

CREATE PROCEDURE GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT ContactID,
    FirstName,
    LastName
FROM Person.Contact
WHERE LastName = @LastName
ORDER BY ContactID

If you just execute this stored procedure in SQL Server Management Studio you get a result set that looks like this:

EXEC GetPeopleByLastName @LastName = 'Alexander'

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

  ContactID FirstName                           LastName
----------- ----------------------------------- -----------------------
         22 J. Phillip                          Alexander
         23 Michelle                            Alexander
        430 Mary                                Alexander
        
 . . . { hiding a bunch of rows } . . .
 
      19942 Morgan                              Alexander

(123 row(s) affected)

 

If you want to write a web page that calls this stored procedure and processes the results that code you'll need to add a using clause for the SqlClient namespace.  This is needed for all the client side samples.

using System.Data.SqlClient;

The code itself might look like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.GetPeopleByLastName"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));
        conn.Open();
        cmd.Connection = conn;
      
        using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (rdr.Read())
            {
                int contactID = rdr.GetInt32(rdr.GetOrdinal("ContactID"));
                string firstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
                Response.Write(firstName + " (" + contactID.ToString() + ")");
            }
            rdr.Close();
        }
    }
}

If you want to capture this result set using T-SQL you'll need a place to store it. Temporary tables work well for that.  That code might look something like this:

DECLARE  @People TABLE (
    ContactID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
)

INSERT @People (ContactID, FirstName, LastName)
EXEC dbo.GetPeopleByLastName @LastName = 'Alexander'

SELECT COUNT(*) FROM @People
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

(123 row(s) affected)
           
-----------
        123

(1 row(s) affected)

This code let's us capture the result set from the stored procedure into a table variable or temporary table.  I use code like this when I work with packaged applications.  I call their stored procedures to get the results I want and then manipulate it as needed.

Using OUTPUT variables

If you just want to return a single value (or a couple of values) you can use output variables.  The stored procedure looks like this:

CREATE PROCEDURE dbo.GetCountByLastName (
    @LastName NVARCHAR(50),
    @LastNameCount INT OUTPUT )
AS
SELECT @LastNameCount = COUNT(*)
FROM Person.Contact
WHERE LastName = @LastName

If we want to return the value using T-SQL we'd use code like this:

DECLARE @TheCount INT

EXEC dbo.GetCountByLastName 
    @LastName = 'Alexander',
    @LastNameCount = @TheCount OUTPUT

SELECT TheCount = @TheCount
GO
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

   TheCount
-----------
        123

(1 row(s) affected)

And we can see that there are still 123 Alexanders in our database.  To call run this stored procedure from ASP.NET we'd need code that looked like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.GetCountByLastName"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@LastName", "Alexander"));

        SqlParameter countParameter = new SqlParameter("@LastNameCount", 0);
        countParameter.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(countParameter);

        conn.Open();
        cmd.Connection = conn;

        cmd.ExecuteNonQuery();
        int count = Int32.Parse(cmd.Parameters["@LastNameCount"].Value.ToString());
        Response.Write("<p>Count: " + count.ToString());
        conn.Close();
    }
}

Using Return

The last way to get data back from a stored procedure is also the most limiting. It only returns a single numeric value. This is most commonly used to return a status result or error code from a procedure. Consider this procedure:

CREATE PROC dbo.TestReturn (@InValue int)
AS
Return @Invalue
GO

All it does is use the RETURN statement to send back the value that was passed in. Note that executing a RETURN statement causes a stored procedure to stop executing and return control back to the calling program. This is often used to test for error conditions and stop processing if one is found. The following script calls the TestReturn stored procedure:

DECLARE @ReturnValue INT
EXEC @ReturnValue = TestReturn 3
SELECT ReturnValue=@ReturnValue

and the output looks like this:

ReturnValue 
----------- 
3

(1 row(s) affected)

Whatever number is returned using the RETURN statement is put into the variable @ReturnValue.  The ASP.NET code to get the return value looks like this:

// Your code should get the connection string from web.config
string connectionString = 
  @"Server=L40\YUKON; Initial Catalog=AdventureWorks; Integrated Security=True;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand("dbo.TestReturn"))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@Invalue", 3));

        SqlParameter returnValue = new SqlParameter("@Return_Value", DbType.Int32);
        returnValue.Direction = ParameterDirection.ReturnValue;

        cmd.Parameters.Add(returnValue);

        conn.Open();
        cmd.Connection = conn;

        cmd.ExecuteNonQuery();
        int count = Int32.Parse(cmd.Parameters["@Return_Value"].Value.ToString());
        Response.Write("<p>Return Code: " + count.ToString());
        conn.Close();
    }
}

Those are the three best ways I know of to get data back from a stored procedure.

Open SSMS From Command Prompt

sqlwb command prompt utility which opens SQL Server Management Studio. sqlwb command does not run queries from command prompt. sqlcmd utility runs queries from command prompt, read for more information.

sqlwb

[scriptfile] [projectfile] [solutionfile]

[-S servername] [-d databasename] [-U username] [-P password]

[-E] [-nosplash] [-?]

I use following script very frequently.

1) Open SQL Server Management Studio.

sqlwb

2) Open SQL Server Management Studio with windows authentication with no splash screen and default database as AdventureWorks

sqlwb -E -S SQLServerName -D AdventureWorks – nosplash

3) Open SQL Server Management Studio with username and password with no splash screen and default database as AdventureWorks

sqlwb -S SQLServerName -U MyUserName -P MyPassWord -D AdventureWorks – nosplash

To execute above commands you should have access to sqlwb.exe file. My local computer has sqlwb file in folder : C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE

Click on Start >> click on run >> type in following command to launch SQL Server Management Studio :

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlwb.exe” -E -S SQL -nosplash

create Multiple tables with Same Name in a Database ?

The answer is YES .
We can create multiple tables with same name under Different Schema(s).

To Identify the Default schema:
Select SCHEMA_NAME() 'Default Schema'
Result is: dbo
Whenever we create a new table, The table will be created under "dbo" schema. Because this is the default schema.

Creating a Table:
Use SQLServerBuddy
Go
Create Table TB_Table1(ID Int Identity,Col1 Varchar(10))
Go
Result is: Command(s) completed successfully.

To Identify the table "TB_Table1" created under which schema
Use SQLServerBuddy
Go
select Object_schema_Name(object_id('[TB_Table1]'))
Result is: dbo

Let's try to Create the Table again:
Use SQLServerBuddy
Go
Create Table TB_Table1(ID Int Identity,Col1 Varchar(10))
Go
Result is:There is already an object named 'TB_Table1' in the database.
Because, The same table TB_Table1 trying to create under default schema "dbo", So the table already exists under the schema. Then how to proceed ?

Let's create a new schema named "SQLBuddy"
Use SQLServerBuddy
Go
CREATE SCHEMA [SQLBuddy]
Result is:Command(s) completed successfully.

Now, We can try to create the same table under "SQLBuddy" schema
Use SQLServerBuddy
Go
Create Table [SQLBuddy].[TB_Table1](ID Int Identity,Col1 Varchar(10))
Result is:Command(s) completed successfully.

Let's validate how many tables created with same name under which Schema(s)
Use SQLServerBuddy
Go
Select [Name],[schema_id] 'Schema ID',SCHEMA_NAME([schema_id]) 'Schema Name' from sys.tables Where [name] ='TB_Table1'

Result is:

Now, Two tables have been created with same name under different Schema(s) one table under "dbo" schema and one table under "SQLBuddy" schema in the same Database "SQLServerBuddy".

Note : SQLServerBuddy is the Database Name.

Conclusion: We can create more than one table with same name under different Schema in a Database.

INSERTED / DELETED tables in a SQL Server trigger

Triggers use two logical tables called the INSERTED and the DELETED table. These tables are accessed only inside triggers and you cannot directly modify the data in the tables. The tables hold the new and old rows of the data being inserted or deleted.

If you DELETE a row , a row will be created in the DELETED table. Similarly if you INSERT a row, a row is created in the INSERTED table. If you update a record with an UPDATE statement, the INSERTED table will contain the updated row and a previous state of the row will be added to the DELETED table.

Note: You cannot use a CREATE INDEX on these tables.

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

The following script to check if any stored procedure modified or created in last 7 days. If SQL server suddenly start behaving in un-expectable behavior and if stored procedure were changed recently, following script can be used to check recently modified stored procedure. If stored procedure was created but never modified afterwards modified date and create date for that stored procedure are same.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 7
----Change 7 to any other day value
 
Following script will provide name of all the stored procedure which were created in last 7 days, they may or may not be modified after that.

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7
----Change 7 to any other day value.
Date condition in above script can be adjusted to retrieve required data.