Determining Table Sizes and the Number of Rows in each Table in a SQL DB

The following script can be used to determine which tables in a database are taking up the most physical space and which have the most rows in them.  The query below orders the results by tables size from largest to smallest, but it's easily updatable to order by row count.  If you're interested in seeing which tables in your database are growing the most, you can create a nightly job to dump the results of this query into a table with a timestamp added.

/*

************************************************
Author:  Chicago Computer Consultants
Date:  5.6.11
Title:  Number of Rows and Table Sizes
Purpose:  Displays the names of all of the tables, numbers of rows, and indexes in a SQL db and their sizes.
************************************************
*/

USE [/*Insert DB Name Here*/]

GO

CREATE TABLE

  #temp (
   table_name sysname ,
   row_count int,
   reserved_size varchar(50),
   data_size varchar(50),
   index_size varchar(50),
   unused_size varchar(50))

SET NOCOUNT ON

INSERT    
    #temp
EXEC      
    sp_msforeachtable 'sp_spaceused ''?'''
SELECT    
    a.table_name AS [Table Name],
    a.row_count AS [Row Count],
    count(*) AS [Column Count],
    a.data_size AS [Data Size],
    a.index_size AS [Index Size]
FROM      
 #temp a
 INNER JOIN information_schema.columns b
  ON
   a.table_name collate database_default = b.table_name collate database_default
GROUP BY  
  a.table_name,
  a.row_count,
  a.data_size,
  a.index_size
ORDER BY  
  CAST(Replace(a.data_size, ' KB', '') as integer) desc
DROP TABLE #temp

Determine and Alter Ownership of All Objects in a SQL Database

The following script can be used to determine which objects in a SQL database are owned by a schema other than the one which is specified in the script and creates, as its output, a list of commands to update the ownership to that aforementioned schema.

Please note:  Just because you're able to change the ownership of objects in a database, doesn't mean that you should, or that this won't have consequences elsewhere.  I've used this script to update objects which were created as part of an older installation/upgrade package where we used create objects by passing along the credentials of the user executing the package, rather than simply creating all objects as belonging to 'dbo'.

This is the basic text of the script:

/*************************************************

Author: Chicago Computer Consultants [Charles E. Brinkman]
Date: 1.05.11
Title: Determine and Alter Ownership of All Objects in a SQL Database
Purpose:     

1. To determine if any objects in a database are associated with schemas other than the one specified in the script.
2. To generate a script capable of updating any objects which do not belong to the desired schema.

**********************************************/
SELECT          
     'ALTER SCHEMA TARGET_SCHEMA
      TRANSFER [' + B.NAME + '].' + A.NAME
FROM
     SYS.OBJECTS A INNER
JOIN          
     SYS.SCHEMAS B
ON
     A .SCHEMA_ID = B.SCHEMA_ID
WHERE
     B .NAME <> 'TARGET_SCHEMA'
AND
     B.NAME <> 'sys'
ORDER BY         
     TYPE_DESC DESC

For example, to search for any objects which are not owned by the schema 'dbo' and to generate a script capable of transfering the ownership of those objects to 'dbo' you would modify the above script to the following by simply replacing both instances of the words "TARGET_SCHEMA" with the word 'dbo':

SELECT          

     'ALTER SCHEMA dbo
     TRANSFER [' + B.NAME + '].' + A.NAME
FROM
     SYS.OBJECTS A INNER
JOIN          
     SYS.SCHEMAS B
ON
     A .SCHEMA_ID = B.SCHEMA_ID
WHERE
     B .NAME <> 'dbo'
AND
     B.NAME <> 'sys'
ORDER BY
     TYPE_DESC DESC

The output of this script will be similar to the following, with one line appearing for each object which is not owned by the dbo schema:

ALTER SCHEMA dbo TRANSFER [test].MY_TABLE_1
ALTER SCHEMA dbo TRANSFER [test].MY_SP_1
 
Copying these results into a new query window and executing them will update the schema of these objects from 'test' to 'dbo'.


Alternately, if you don't care about knowing which objects' ownership will be updated, you can also use the suggestion in this link from MS, which creates a SP to do this for you, but doesn't provide you with the same detail by way of feedback.  Let me know if you have any questions or run into any problems with the script, it's been very helpful for me in the past.

Display Names and Locations of SQL Database Files and DB Owners

As part of maintaining shared development servers used by many programmers, PMs and other technical personnel, I frequently check that users aren’t placing SQL database files in places where they shouldn’t be.  As I rapidly grew tired of monitoring this using other means, I came up with the script below which displays the names of all of the SQL databases on a SQL Server instance, their data and transaction log files, their owner, and their physical location.   I hope that this will be helpful to anyone who finds themselves in a similar situation.

/*************************************************

Author:     Chicago Computer Consultants [Charles E. Brinkman]

Date:       12.17.10   

Title:      Find Physical Files Associated with SQL DBS

Purpose:    To display displays the names of all of the SQL databases on a SQL Server instance, their data and transaction log files, their owner, and their physical location.

*************************************************/

SELECT

      sys.databases.name AS [Database Name],

      suser_sname(owner_sid) AS [Owner Name],

      sys.master_files.name AS [File Name],

      sys.master_files.physical_name AS Location

FROM sys.databases

      RIGHT JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id

/*Update the ORDER BY values below to display the results in a way that will be helpful to you*/

ORDER BY Location, sys.databases.name