Make your own free website on Tripod.com

MKoD - D Programming Language

A useful query to gather Columns and Datatypes counts - code-name: sp_Sum_of_Columns_and_Datatypes.SQL

Very Kool! A query that looks only at user tables in a database.

-- ==========================================================
-- sp_Sum_of_Columns_and_Datatypes.SQL
-- Author        : David L. 'SpottedTiger' Davis 
-- Date Created  : 02.Aug.01
-- Date Modified : 25.Aug.04 corrected some errors 
-- Query gathers data about a database's columns and datatypes.
-- T-SQL MS SQL Server v7.x 'n' 2000
-- ==========================================================
--
-- ===================================================
-- Gathers the totals datatypes used in the User Table
-- ===================================================
SELECT DISTINCT 
  (SELECT COUNT(so2.name)
     FROM sysobjects AS so2
    WHERE OBJECTPROPERTY(so2.id, N'IsUserTable') = 1
  AND SUBSTRING(so2.name, 1, 3) <> 'tbl') AS [# of Tables],

  -- varchar  = 167
  -- char     = 175
  -- nvarchar = 231 (same as sysname)
  -- nchar    = 239
  (SELECT COUNT(sc1.xtype)
     FROM sysobjects AS so1 
          INNER JOIN
          syscolumns AS sc1
          ON so1.id = sc1.id
    WHERE OBJECTPROPERTY(so1.id, N'IsUserTable') = 1
      AND sc1.xtype IN (167, 175, 231, 239)) AS [Cols w/VarChars],

  -- int      = 56
  -- bigint   = 127
  -- smallint = 52
  -- tinyint  = 48
  -- uniqueidentitier = 36 
  (SELECT COUNT(sc2.xtype)
     FROM sysobjects AS so2 
          INNER JOIN
          syscolumns AS sc2
          ON so2.id = sc2.id
    WHERE OBJECTPROPERTY(so2.id, N'IsUserTable') = 1
      AND sc2.xtype IN(56, 127, 52, 48, 36)) AS [Cols w/Integers],

  -- datetime      = 61
  -- smalldatetime = 58
  -- timestamp     = 189
  (SELECT COUNT(sc3.xtype)
     FROM sysobjects AS so3 
          INNER JOIN
          syscolumns AS sc3
          ON so3.id = sc3.id
    WHERE OBJECTPROPERTY(so3.id, N'IsUserTable') = 1
      AND sc3.xtype IN (61, 58, 189)) AS [Cols w/DateTimes],

  -- float      =  62
  -- numeric    = 108
  -- decimal    = 106
  -- money      =  60
  -- real       =  59
  -- smallmoney = 122
  (SELECT COUNT(sc4.xtype)
     FROM sysobjects AS so4 
          INNER JOIN
          syscolumns AS sc4
          ON so4.id = sc4.id
    WHERE OBJECTPROPERTY(so4.id, N'IsUserTable') = 1
      AND sc4.xtype IN (62, 108, 106, 60, 59, 122)) AS [Cols w/Floats],

  -- binary    = 173
  -- image     =  34
  -- ntext     =  99
  -- text      =  35
  -- varbinary = 165
  (SELECT COUNT(sc5.xtype)
     FROM sysobjects AS so5 
          INNER JOIN
          syscolumns AS sc5
          ON so5.id = sc5.id
    WHERE OBJECTPROPERTY(so5.id, N'IsUserTable') = 1
      AND sc5.xtype IN (173, 34, 99, 35, 165)) AS [Cols w/Memos],

  -- bit         = 104
  -- sql_variant =  98
  -- sysname     = 231 (same as nvarchar: counted in the [Cols w/VarChars column]
  (SELECT COUNT(sc6.xtype)
     FROM sysobjects AS so6 
          INNER JOIN
          syscolumns AS sc6
          ON so6.id = sc6.id
    WHERE OBJECTPROPERTY(so6.id, N'IsUserTable') = 1
      AND sc6.xtype IN (104, 98)) AS [Cols w/Others]

-- ======================================================
-- Show the overall number of columns in User Tables
-- ======================================================
SELECT DISTINCT COUNT(sc.id) AS [Total# Cols in the Database]
  FROM sysobjects AS so 
         INNER JOIN
       syscolumns AS sc
         ON so.id = sc.id
WHERE OBJECTPROPERTY(so.id, N'IsUserTable') = 1
--GROUP BY so.id, so.[name]
--ORDER BY so.[name]

-- ======================================================
-- Show all User Tables and the number of columns in each
-- ======================================================
SELECT DISTINCT so.id AS [SQL ID#], so.[name] AS [Table Name], COUNT(sc.id) AS [Total# Cols]
  FROM sysobjects AS so 
         INNER JOIN
       syscolumns AS sc
         ON so.id = sc.id
WHERE OBJECTPROPERTY(so.id, N'IsUserTable') = 1
GROUP BY so.id, so.[name]
ORDER BY so.[name]

-- ======================================================
-- Show all User Tables and the number of columns in each
--  and the number of datatypes in that table.
-- ======================================================
SELECT DISTINCT so.id AS [SQL ID#], 
  so.[name] AS [Table Name], 
  COUNT(sc.id) AS [Total# Cols], 

  -- varchar  = 167
  -- char     = 175
  -- nvarchar = 231
  -- nchar    = 239 (same as sysname)
  (SELECT COUNT(xtype)
     FROM syscolumns AS a
    WHERE a.xtype IN (167, 175, 231, 239) AND
          so.id = a.id) AS VarChars,

  -- int      = 56
  -- bigint   = 127
  -- smallint = 52
  -- tinyint  = 48
  -- uniqueidentitier = 36 
  (SELECT COUNT(xtype)
     FROM syscolumns AS b
    WHERE b.xtype IN(56, 127, 52, 48, 36) AND
          so.id = b.id) AS Integers,

  -- datetime      = 61
  -- smalldatetime = 58
  -- timestamp     = 189
  (SELECT COUNT(xtype)
     FROM syscolumns AS c
    WHERE c.xtype IN (61, 58, 189) AND
          so.id = c.id) AS DateTimes,

  -- float      =  62
  -- numeric    = 108
  -- decimal    = 106
  -- money      =  60
  -- real       =  59
  -- smallmoney = 122
  (SELECT COUNT(xtype)
     FROM syscolumns AS d
    WHERE d.xtype IN (62, 108, 106, 60, 59, 122) AND
          so.id = d.id) AS Floats,

  -- binary    = 173
  -- image     =  34
  -- ntext     =  99
  -- text      =  35
  -- varbinary = 165
  (SELECT COUNT(xtype)
     FROM syscolumns AS e
    WHERE e.xtype IN (173, 34, 99, 35, 165) AND
          so.id = e.id) AS Memos,

  -- bit         = 104
  -- sql_variant =  98
  -- sysname     = 231 (same as nvarchar: counted in the [Cols w/VarChars column]
  (SELECT COUNT(xtype)
     FROM syscolumns AS f
    WHERE f.xtype IN (104, 98) AND
          so.id = f.id) AS Others

  FROM sysobjects AS so 
         INNER JOIN
       syscolumns AS sc
         ON so.id = sc.id
WHERE OBJECTPROPERTY(so.id, N'IsUserTable') = 1
  AND SUBSTRING(so.name, 1, 3) <> 'tbl'
GROUP BY so.id, so.[name]
ORDER BY so.[name]

* Output from running this query against Microsoft's Northwind example database on MS SQL Server 2000

# of Tables Cols w/VarChars Cols w/Integers Cols w/DateTimes Cols w/Floats Cols w/Memos Cols w/Others 
----------- --------------- --------------- ---------------- ------------- ------------ ------------- 
13          52              20              5                4             6            1

(1 row(s) affected)

Total# Cols in the Database 
--------------------------- 
88

(1 row(s) affected)

SQL ID#     Table Name              Total# Cols 
----------- ----------------------- ----------- 
2041058307  Categories              4
853578079   CustomerCustomerDemo    2
869578136   CustomerDemographics    2
2073058421  Customers               11
1977058079  Employees               18
917578307   EmployeeTerritories     2
325576198   Order Details           5
21575115    Orders                  14
117575457   Products                10
885578193   Region                  2
2105058535  Shippers                3
2137058649  Suppliers               12
901578250   Territories             3

(13 row(s) affected)

SQL ID#     Table Name              Total# Cols VarChars    Integers    DateTimes   Floats      Memos       Others      
----------- ----------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
2041058307  Categories              4           1           1           0           0           2           0
853578079   CustomerCustomerDemo    2           2           0           0           0           0           0
869578136   CustomerDemographics    2           1           0           0           0           1           0
2073058421  Customers               11          11          0           0           0           0           0
1977058079  Employees               18          12          2           2           0           2           0
917578307   EmployeeTerritories     2           1           1           0           0           0           0
325576198   Order Details           5           0           3           0           2           0           0
21575115    Orders                  14          7           3           3           1           0           0
117575457   Products                10          2           6           0           1           0           1
885578193   Region                  2           1           1           0           0           0           0
2105058535  Shippers                3           2           1           0           0           0           0
2137058649  Suppliers               12          10          1           0           0           1           0
901578250   Territories             3           2           1           0           0           0           0

(13 row(s) affected)
Mars: fourth Rock from the Sun.