Using the technique I discussed in this post, the following SQL Server stored procedure can be used to display the number of rows in each table and used/unused space for all tables in a specified database.  Put the stored procedure into whatever database you use for your Utilities database, then run the procedure from anywhere, passing in the name of the database for which to display row counts.

CREATE PROCEDURE p_show_row_counts_and_table_sizes(@dbname VARCHAR(255) = NULL)
AS
BEGIN
DECLARE @sql VARCHAR(MAX)

SET @sql = '
SELECT 
    COALESCE(t.NAME, ''~~GRAND TOTAL:'') AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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 
    GROUPING SETS
    (
        (t.Name, s.Name, p.Rows),
        ()
    )
ORDER BY 
    t.Name'

IF @dbname IS NOT NULL
BEGIN
    SET @sql = 'USE ' + @dbname + ' ' + @sql
END
EXEC (@sql)
END

Note: The contents of the “@sql” SQL Query used inside this stored procedure is from the StackOverflow.com answer by “marc_s” on Oct 25 ’11 (edited Oct 29 ’13) to the question “Get size of all tables in database“. My contribution was simply to turn that query into a stored procedure that could access any database and to add the “Grand Total” values.

Using the technique I discussed in this post, the following SQL Server stored procedure can be used to display the space used for all tables in a specified database.  Put the stored procedure into whatever database you use for your Utilities database, then run the procedure from anywhere, passing in the name of the database for which to display table usage information.

CREATE PROCEDURE p_show_spaceused_all_tables(@dbname VARCHAR(255) = NULL)
AS
BEGIN

DECLARE @sql VARCHAR(MAX)

SET @sql = '
SET NOCOUNT ON 

CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable ''EXEC sp_spaceused ''''?'''''' 

SELECT *
FROM   #t
UNION 
SELECT ''~~GRAND TOTAL'' AS name, null AS rows,
       CAST(SUM(CAST(REPLACE(reserved, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS reserved,
       CAST(SUM(CAST(REPLACE(data, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS data,
       CAST(SUM(CAST(REPLACE(index_size, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS index_size,
       CAST(SUM(CAST(REPLACE(unused, ''KB'', '''') AS INT)) AS VARCHAR(18)) + '' KB'' AS unused
FROM   #t
ORDER BY name

DROP TABLE #t'


IF @dbname IS NOT NULL
BEGIN
    SET @sql = 'USE ' + @dbname + ' ' + @sql
END

EXEC (@sql)
END

Note: The contents of the “@sql” SQL Query used inside this stored procedure is from Alexander Grob’s article “How to Obtain the Size of All Tables in a SQL Server Database“. My contribution was simply to turn that query into a stored procedure that could access any database and to add the “Grand Total” values.

Running a SQL Server Stored Procedure in the context of an arbitrary database that is specified at runtime is usually very difficult. By using dynamic SQL, however, you can pass a database name as a parameter to the stored procedure and run the SQL code inside that stored procedure in the context of the specified database. Dynamic SQL gets a bad rap, but in a controlled environment, dynamic SQL is not evil and it can be an invaluable tool.

Details

Every SQL Server DBA has a library of scripts to provide insight into their servers. Most of this information is server-wide or process-level information so the database context in which these scripts run is irrelevant. As a result, these scripts can be compiled into stored procedures and tucked away in a “Utility” database, then run from any database.

However, some scripts need to collect information from an arbitrary database, where the name of the target database changes or needs to be specified at execution time. For example, a generic script to list the space used by every table in a database or to show the number of rows in each table in a database would need to be able to run against any database. Generic scripts like these cannot normally be run as stored procedures because they will only reflect the tables in the database that actually holds the stored procedure — the database in which the stored procedure was compiled.

Working around this problem is usually done in one of two ways:

• Put a copy of each of these stored procedures into each target database, then execute the copy in that database to get information for that database.
• For infrequently-used scripts, run the script interactively in SQL Management Studio.

Neither one of these methods is a good solution. Running scripts interactively though SQL Management Studio doesn’t scale. It works when you have a few servers with a few databases or where all your servers can be connected via a single instance of SQL Management Studio, but it fails in a larger or more complex environment. For example, in our environment we were responsible for several hundred servers that were all running in remote client offices and where we could only connect to each server via a remote SSH command line. SQL Management Studio worked fine in the data center but in the field it was just not an option.

Putting copies of the stored procedures into each database is a better alternative, but only because “it stinks less”. It is still relatively horrible for many reasons, including polluting your application and user databases with “utility” procedures, the need to maintain all those procedure versions in all those databases, and the need to populate your utility procedures into every new database you create. Being able to run these types of scripts as single instances of stored procedures in a Utility database is a MUCH better solution.

Enter Dynamic SQL.  (Stop booing and hissing – dynamic SQL really is not _always_ evil.)

Dynamic SQL — a string of SQL that is run by the “EXEC( .. )” TSQL command — will compile and run whatever SQL it is given. That SQL can include a “USE database” statement, thus allowing the SQL code to change to another database before running the remaining SQL code. Running a SQL command in another database is as simple as running something like the following from ANY database context, including from inside a stored procedure that lives anywhere:

EXEC( 'USE MyOtherDatabase SELECT DB_NAME()' )  -- prints "MyOtherDatabase"

Using this technique, you can put each of those generic scripts into their own stored procedure, add a parameter to each stored procedure to accept a database name, put one single copy of the stored procedure into your Utility database, then call the stored procedure from anywhere, passing in the name of the database in which you want it to operate.

For example, the following “traditional” example fails (aka: “it behaves as expected”) – the “SELECT DB_NAME()” prints the name of the database that contains the stored procedure rather than the name of the database from which the procedure is being run:

USE utility
GO
CREATE PROCEDURE p_fail_static
AS
SELECT DB_NAME()

USE master
GO
EXEC utility..p_fail_static  -- prints "utility", proving that its context is still the "utility" database rather than "master"

However, the following “dynamic” example does what we need. The “SELECT DB_NAME()” in this case prints the name of the database that was passed to the procedure as a parameter, regardless of where the stored procedure lives or from where we run it:

USE utility
GO
CREATE PROCEDURE p_test_dynamic(@dbname VARCHAR(255) = NULL)
AS
DECLARE @sql VARCHAR(MAX)
SET @sql = 'USE ' + @dbname + ' SELECT DB_NAME()'
EXEC (@sql)

USE master
GO
EXEC utility..p_test_dynamic 'model'   -- prints "model"
EXEC utility..p_test_dynamic 'tempdb'  -- prints "tempdb"
EXEC utility..p_test_dynamic 'master'  -- prints "master"

Using this technique, we can encapsulate a SQL script inside a stored procedure but at run time specify the database in which we want that script to run. (See this post and this post for real-world examples of this technique.)

Just remember that you ARE still running dynamic SQL so there are additional things to keep in mind such as security considerations, user-context considerations, etc. But as long as you will be executing the stored procedure from an admin-level user account that already has access to the underlying data, and as long as the stored procedure itself is locked down to only allow execution by admin-level user accounts, this is a safe and effective way to solve an otherwise-messy problem.

Note: This technique won’t work on Azure SQL Databases. On Azure SQL Database, the database name in the USE statement can only refer to the current database; the only way to change an Azure SQL Database context to a different database is to connect directly to that other database.