Count Records for Each Table in Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Count Records for Each Table in Database

Author

Topic

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 15:42:15

Hi all,

I received this store procedure and could not find the author.(listed below) I will like to know how to take the results and store them in the a table.

Any all help will be appreciated

use Northwind
go
create procedure ColumnCount ( @sObject sysname = null, @sColumn
sysname = null )
as
declare @Sql varchar(1000)
declare csrTable cursor local fast_forward for

select table_name from
information_schema.tables where (table_name like @sObject or @sObject is null) and
table_type = 'BASE TABLE' order by table_name

open csrTable fetch next from csrTable into @sObject

while (@@fetch_status = 0) begin declare csrColumn cursor local fast_forward for
select column_name from information_schema.columns
where table_name = @sObject
and(column_name like @sColumn or @sColumn is null) and data_type not in ('text',
'ntext', 'image')
order by ordinal_position

open csrColumn fetch next from csrColumn into @sColumn

while (@@fetch_status = 0) begin select @Sql = 'select [' + @sColumn + '],
count(*) ' + 'from [' + @sObject + '] ' + 'group by [' + @sColumn + '] ' + 'order
by [' + @sColumn + ']' print @Sql exec (@Sql) fetch next from csrColumn into
@sColumn end close csrColumn deallocate csrColumn set @sColumn = null fetch next
from csrTable into @sObject end close csrTable deallocate csrTable
go

exec columncount 'Orders', 'CustomerID'
exec columncount 'Employees'
exec columncount
go drop procedure columncount

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-15 : 15:46:13

What problem are you trying to solve? Could you format your code with code tags so that it is readable?

Tara

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 15:54:16

I am trying to take the results and store them in table or an excel spreadsheet?
what do you mean by code tags?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-15 : 16:08:20

When posting code, use code tags around your code to keep the format:

[ c o d e ]

code goes here

[ / c o d e ]

Do this without the spaces.

Your problem is probably easily solved by doing this though:

INSERT INTO Table1(...)
exec columncount...

Tara

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 16:49:25

Each table that is counted the column length will be different. How can you accomplish this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-15 : 16:55:05

I don't see how that matters. Please explain further.

Tara

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 18:02:30

When I execute

 exec columncount

all I get in the output in QA is the select statement it builds for every column in the table
for example:

select [ADDR_NBR],count(*) from [ADDRESS] group by [ADDR_NBR] orderby [ADDR_NBR]

select [HOUSE_NBR],
count(*) from [ADDRESS] group by [HOUSE_NBR] order
by [HOUSE_NBR]

select [PREFIX],
count(*) from [ADDRESS] group by [PREFIX] order
by [PREFIX]

When I excute

exec Columncount'address','house_nbr'

I get the results in the QA grid with the Colunm name and count for each column. Since each column will have a diffrent name in the table how will I be able to see the columnname using your sample. If I create the table to insert to; how can I store the results and the column name.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-15 : 18:06:39

Modify the output of the stored procedure so that it outputs columnname and columncount. Then:

CREATE TABLE MyTable (ColumnName nvarchar(256) NOT NULL, ColumnCount INT NOT NULL)

INSERT INTO MyTable(ColumnName, ColumnCount)
EXEC Columncount 'address', 'house'

Tara

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 18:33:18

I really appreciate your time. The example you gave will work fine when I specific the column name. What I need is to get the column name as variable like

INSERT INTO MyTable(ColumnName, ColumnCount)
EXEC Columncount 'address', '(<variable column name>)'

This will not work

INSERT INTO MyTable(ColumnName, ColumnCount)
EXEC Columncount 'address'

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 18:43:17

If I create the orginal code

use Northwind 
go
create procedure ColumnCount ( @sObject sysname = null, @sColumn
sysname = null )
as
declare @Sql varchar(1000)
declare csrTable cursor local fast_forward for

select table_name from
information_schema.tables where (table_name like @sObject or @sObject is null) and
table_type = 'BASE TABLE' order by table_name

open csrTable fetch next from csrTable into @sObject

while (@@fetch_status = 0) begin declare csrColumn cursor local fast_forward for
select column_name from information_schema.columns 
where table_name = @sObject 
and(column_name like @sColumn or @sColumn is null) and data_type not in ('text',
'ntext', 'image')
order by ordinal_position

open csrColumn fetch next from csrColumn into @sColumn

while (@@fetch_status = 0) begin select @Sql = 'select [' + @sColumn + '],
count(*) ' + 'from [' + @sObject + '] ' + 'group by [' + @sColumn + '] ' + 'order
by [' + @sColumn + ']' print @Sql exec (@Sql) fetch next from csrColumn into
@sColumn end close csrColumn deallocate csrColumn set @sColumn = null fetch next
from csrTable into @sObject end close csrTable deallocate csrTable 
go

and then execute

exec columncount

I get the result in grid broken down by table name and column name, which brings me back to my original request and help. How can I store the (grid) results to a table. If you have the sample northwind table can you give it a try. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-15 : 19:06:16

I do not have Northwind where I am at right now. But all that you need to do is create a table that matches the result set in Query Analyzer. So if you have TableName, ColumnName, ColumnCount in grid mode, then create a table just like that. Give it appropriate data types. Then

insert into MyTable(...)
exec columncount

That'll insert the results of columncount stored procedure into your table.

Tara

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 19:09:47

tduggan- like I said before I really do appreciate every moment you spent on this. I found a script that gives me everything I need
Posted - 04/14/2003 : 06:33:27
--------------------------------------------------------------------------------

This script gives a list of all the columns in the database and the number of distinct values in them. This can be very useful if you take over a database and you want to find out if the columns are actually used. It might also come in handy if you need some information about the cardinality of your columns to help you design indexes.

It also gives information about the largest size of data in a column and the actual size of the column, so you can find out if those text columns are really necessary for example.

Although the script includes a few performance optimisations, it is not advisable to run it against a production database, as it will perform a table scan on all the tables multiple times.

Jacco

DECLARE @table sysname
DECLARE @column sysname
DECLARE @datatype sysname
DECLARE @designed_length int
DECLARE @all_count int
DECLARE @sql nvarchar(4000)

SET NOCOUNT ON
EXEC sp_updatestats

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- Will speed things up a bit

CREATE TABLE #table_info
(table_name sysname NOT NULL
,column_name sysname NOT NULL
,data_type sysname NOT NULL
,designed_length int NULL
,max_length int NULL
,distinct_count int NULL
,all_count int NOT NULL
,cardinality AS
CASE
WHEN distinct_count IS NULL THEN CAST(data_type AS varchar(7))
WHEN all_count = 0 THEN CAST('No rows' AS varchar(7))
ELSE CAST(
CAST(CAST(distinct_count AS decimal)/CAST(all_count AS decimal) AS decimal(18,4)) AS varchar(7))
END
)

DECLARE c CURSOR FAST_FORWARD FOR
SELECT
isc.table_name,
isc.column_name,
isc.data_type,
COALESCE(isc.character_maximum_length, isc.numeric_precision),
si.rowcnt
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
INNER JOIN sysindexes si
ON isc.table_name = OBJECT_NAME(si.id)
WHERE ist.table_type = 'base table'
AND ist.table_name not like 'dt%'
AND si.indid IN (0,1)
ORDER BY isc.table_name, isc.column_name

OPEN c
FETCH NEXT FROM c INTO @table, @column, @datatype, @designed_length, @all_count
WHILE @@FETCH_STATUS = 0
BEGIN
IF @datatype IN ('text', 'ntext', 'image')
BEGIN
SET @sql = 'SELECT ''' + @table + ''', ''' + @column + ''', ''' + @datatype + ''''
SET @sql = @sql + ', ' + CAST(@designed_length AS varchar(10)) + ', MAX(DATALENGTH([' + @column + ']))'
SET @sql = @sql + ', NULL' + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table + ']'
END
ELSE
BEGIN
SET @sql = 'SELECT ''' + @table + ''', ''' + @column + ''', ''' + @datatype + ''''
SET @sql = @sql + ', ' + CAST(@designed_length AS varchar(10)) + ', MAX(LEN(CAST([' + @column + '] AS VARCHAR(8000))))'
SET @sql = @sql + ', COUNT(DISTINCT [' + @column + '])'
SET @sql = @sql + ', ' + CAST(@all_count AS varchar(10)) + ' FROM [' + @table + ']'
END
PRINT @sql
INSERT INTO #table_info (table_name, column_name, data_type, designed_length, max_length, distinct_count, all_count)
EXEC(@sql)
FETCH NEXT FROM c INTO @table, @column, @datatype, @designed_length, @all_count
END
CLOSE c
DEALLOCATE c

SELECT table_name, column_name, data_type, designed_length, max_length, distinct_count, all_count, cardinality
FROM #table_info

SET TRANSACTION ISOLATION LEVEL READ COMMITTED ][/code]

DROP TABLE #table_info

I will continue to work on the original script
--------------------------------------------------------------------------------
Edited by - Jacco on 04/17/2003 08:42:26

jemacc
Starting Member

42 Posts

Posted - 2004-10-15 : 19:19:35

Here is the additonal improvement for the select portion of the script

SELECT isc.table_name, isc.column_name, isc.data_type
FROM   information_schema.columns isc
       INNER JOIN information_schema.tables ist
            ON isc.table_name = ist.table_name
WHERE  OBJECTPROPERTY(OBJECT_ID(ist.table_name), N'IsUserTable') = 1 
AND    OBJECTPROPERTY(OBJECT_ID(ist.table_name), N'IsMSShipped') = 0
AND    DATA_TYPE NOT IN ('text', 'ntext', 'image')
ORDER BY isc.table_name, isc.column_name

Kristen
Test

22859 Posts

Posted - 2004-10-16 : 03:30:01

If its of interest the "reverse engineer" script I use here to give me a leg-up when looking at infamiliar data does:


USE Northwind
GO
DECLARE	@strSQL varchar(8000), 
	@strTable varchar(255), 
	@strColumn varchar(255)
SELECT	@strTable = 'Customers', 
	@strColumn = 'Fax'
SELECT @strSQL = 
	'SELECT [Column] = ''' + @strColumn + ''','
	+ ' [Distinct] = COUNT(DISTINCT [' + @strColumn + ']),'
	+ ' [MaxLen] = MAX(DATALENGTH([' + @strColumn + '])),'
	+ ' [NullCount] = SUM(CASE WHEN [' + @strColumn + '] IS NULL THEN 1 ELSE 0 END),'
	+ ' [IsDate] = SUM(IsDate([' + @strColumn + '])),'
	+ ' [IsNumeric] = SUM(IsNumeric([' + @strColumn + ']))'
	+ ' FROM [' + @strTable + ']'
SELECT @strSQL
EXEC (@strSQL)

(It needs to be in a cursor loop that walks around all the tables aand columns, of course)

It often reveals Text columns that are dates/numbers and so on ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources