Find the row counts for all the tables in a SQL Server 2005 database

Have you ever needed to investigate an application database and quickly determine where the records are?
The following script returns a rowset that contains all the table names of a SQL Server 2005 database along with the row counts.

---

-- declare the variables

DECLARE @i INT, -- integer holder

  @table_name VARCHAR(50), -- table name

  @sql NVARCHAR(800) -- dynamic sql

-- create the temp table. Initialize count column to -1

CREATE TABLE #t (

[name] NVARCHAR(128),

rows CHAR(11),

reserved VARCHAR(18),

data VARCHAR(18),

index_size VARCHAR(18),

unused VARCHAR(18)

)

SELECT @table_name = [name]

  FROM sysobjects

  WHERE xtype = 'U'

-- Initialize i to run at least once

SET @i = 1

-- loop while rows are still being selected

WHILE (@i > 0)

BEGIN

 -- create the dynamic sql that updates the row counts

 -- for each table

SET @sql = 'INSERT #t ([name], rows, reserved, data, index_size, unused)

EXEC sp_spaceused ['+ @table_name + ']'

 -- execute the dynamic sql

 EXEC sp_executesql @sql

 -- find out the name of the next table 

 SELECT @table_name = [name]

  FROM sysobjects

  WHERE xtype = 'U'

  AND [name] NOT IN

     (

        SELECT [name]

FROM #t

     ) 

 -- stop looking if no rows are selected

 SET @i = @@ROWCOUNT

END

-- return the results

SELECT *

FROM #t

ORDER BY reserved DESC

DROP TABLE #t

Script to change object owners

I have recently started working with SQL Server 2005, and the first thing I noticed is that I cannot automatically set the owner of a script at creation time.  SQL Server 2005 automatically adds my user name as the object owner.

The following script, adapted from Scott Forsyth's blog, batches up the changes.  Change the name of the old owner (@old) and the new owner (@new) and it will change the ownership for all the database objects.

Script to update SQL Server Object Ownership

DECLARE @old nvarchar(30), @new nvarchar(30), @obj nvarchar(50), @x int; SET @old = 'domain\user-name'; SET @new = 'dbo'SELECT @x = count(*); FROM INFORMATION_SCHEMA.ROUTINES a WHERE   a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')   AND a.SPECIFIC_SCHEMA = @old; while(@x > 0)   BEGIN     SELECT @obj = [SPECIFIC_NAME]     FROM INFORMATION_SCHEMA.ROUTINES a     WHERE        a.ROUTINE_TYPE IN('PROCEDURE', 'FUNCTION')        AND a.SPECIFIC_SCHEMA = @old;      SET @x = @@ROWCOUNT - 1;     EXEC sp_changeobjectowner @obj, @new; END