April 11, 2006 at 12:53 pm
Does any one know whether a system stored procedure exits that would list all tables in a database with row counts and column descriptions ?
April 12, 2006 at 5:17 am
I use DB Sleuth. It is a MSSQL database utility. You can get the row counts and monitor the change in row counts over time. I find it useful to study data propogation. It is available at http://www.dbsleuth.com.
April 12, 2006 at 8:27 am
This should work for what I think you want.
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[makeDataDictionary] as
DECLARE @table_name nvarchar(128)
DECLARE @strTable nvarchar(256)
SET NOCOUNT ON
DECLARE tablenames_cursor CURSOR FOR
SELECT name FROM sysobjects where type = 'U' order by name
OPEN tablenames_cursor
FETCH NEXT FROM tablenames_cursor INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @strTable = 'select ''Total Rows for TABLE [' + @table_name + '] : '' + cast(COUNT(*) as nvarchar(10)) from [' + @table_name + ']'
print @strTable
exec (@strTable)
PRINT 'COLUMNS FOR TABLE ' + @table_name
exec get_column_details @table_name
FETCH NEXT FROM tablenames_cursor INTO @table_name
END
CLOSE tablenames_cursor
DEALLOCATE tablenames_cursor
GO
create procedure [dbo].[get_column_details] @table_name nvarchar(128)
as
select
cast(o.[name] as char(30)) as 'table_name',
cast(c.[name] as char(30)) as 'column_name',
case
when c.[xtype] = 34 then 'image '
when c.[xtype] = 56 then 'int '
when c.[xtype] = 58 then 'smalldatetime'
when c.[xtype] = 108 then 'numeric '
when c.[xtype] = 175 then 'char '
when c.[xtype] = 231 then 'nvarchar '
when c.[xtype] = 239 then 'nchar '
else cast(c.[xtype] as char(12))
end as 'column_type',
cast(c.[length] as char(30)) as 'column_length ',
cast(e.value as char(30)) as 'column_description'
from sysobjects o inner join syscolumns c on o.id = c.id
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
N'user',N'dbo',N'table',@table_name, N'column', null) e on c.name COLLATE Latin1_General_CI_AS = e.objname
where o.name = @table_name
order by c.colorder
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
April 12, 2006 at 9:30 am
Actually, this is a better way to show the column type
alter procedure [dbo].[get_column_details] @table_name nvarchar(128)
as
select
cast(o.[name] as char(30)) as 'table_name',
cast(c.[name] as char(30)) as 'column_name',
cast(t.[name] as char(30)) as 'column_type ',
cast(c.[length] as char(30)) as 'column_length ',
cast(e.value as char(30)) as 'column_description'
from sysobjects o inner join syscolumns c on o.id = c.id
left join ::FN_LISTEXTENDEDPROPERTY(N'MS_Description',
N'user',N'dbo',N'table',@table_name, N'column', null) e on c.name COLLATE Latin1_General_CI_AS = e.objname
left join systypes t on c.xtype = t.xtype
where o.name = @table_name
order by c.colorder
April 12, 2006 at 11:41 am
See discussion at
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=271576#bm271664
there are several suggestions including ones using sp_MSforeachtable
Regards,Yelena Varsha
April 14, 2006 at 7:22 am
I note another change that should be made regarding get_column_details.
xtype for nvarchar and sysname are the same in the systypes table, so you should make the following change or you get 2 rows for all nvarchar types...
left join systypes t on c.xtype = t.xtype
should be changed to
left join systypes t on c.xtype = t.xusertype
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply