Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Database Object Counts - SQL Server

RSS
Modified on Fri, Jan 09, 2009, 2:57 PM by Administrator Categorized as Code Sample, SQL Server
The following T-SQL script will list all object types with the number of objects of each type within the current database.

select 
    SORT_ORDER = case type
        when 'C'  then 60
        when 'D'  then 30
        when 'F'  then 40
        when 'FN' then 100
        when 'IF' then 100
        when 'K'  then 50
        when 'P'  then 90
        when 'S'  then 70
        when 'TF' then 100
        when 'TR' then 20
        when 'U'  then 10
        when 'V'  then 80
        else 110
        end, 
   
    TYPE = case type
        when 'C'  then 'check constraints'
        when 'D'  then 'defaults'
        when 'F'  then 'foreign keys'
        when 'FN' then 'functions'
        when 'IF' then 'functions'
        when 'K'  then 'primary keys'
        when 'P'  then 'stored procedures'
        when 'S'  then 'system tables'
        when 'TF' then 'functions'
        when 'TR' then 'triggers'
        when 'U'  then 'user tables'
        when 'V'  then 'views'
        else type
    end,

    QTY = count(1)

from 
    sysobjects
 
group by
    case type
        when 'C'  then 60
        when 'D'  then 30
        when 'F'  then 40
        when 'FN' then 100
        when 'IF' then 100
        when 'K'  then 50
        when 'P'  then 90
        when 'S'  then 70
        when 'TF' then 100
        when 'TR' then 20
        when 'U'  then 10
        when 'V'  then 80
        else 110
        end, 
     case type
        when 'C'  then 'check constraints'
        when 'D'  then 'defaults'
        when 'F'  then 'foreign keys'
        when 'FN' then 'functions'
        when 'IF' then 'functions'
        when 'K'  then 'primary keys'
        when 'P'  then 'stored procedures'
        when 'S'  then 'system tables'
        when 'TF' then 'functions'
        when 'TR' then 'triggers'
        when 'U'  then 'user tables'
        when 'V'  then 'views'
        else type
    end
order by 1

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.