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)
Paths
