в офисе жарко и душно а на улице холодно и -23
жизнь как лихорадка, блин =)

а вот скрипт для mssql, который вытаскивает из базы все таблицы, колонки и их описания и выстраивает как текстовый фаел: (ващето написан спецом для query analyser, mssql 2000)


----------------------------------------
-- listing of all tables/columns
-- copyleft 2007 scaven
-- last modified: 20071112
----------------------------------------

DECLARE tables_cursor CURSOR
FOR
SELECT so.name, so.id FROM sysobjects so where so.type like 'U' order by so.name

DECLARE col_cursor CURSOR
FOR
SELECT sc.name, sc.id, sc.isnullable FROM syscolumns sc

DECLARE @tablename sysname
DECLARE @tableprevname sysname
DECLARE @tableid int
DECLARE @colname sysname
DECLARE @colid int
DECLARE @colisnullable int
DECLARE @descr varchar(200)

print '---------'
print '*********'
print ' TABLES '
print '*********'
print '---------'
OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @tablename, @tableid
WHILE @@FETCH_STATUS=0
BEGIN
set @descr=CONVERT(varchar(200), (SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user','dbo','table',@tablename,null, null)))
if @descr is not null and @descr not like '' set @descr=' - '+@descr else set @descr=' '
print @tablename+@descr
FETCH NEXT FROM tables_cursor INTO @tablename, @tableid
END
CLOSE tables_cursor
print ''
print ''
print '---------'
print '*********'
print ' COLUMNS '
print '*********'
OPEN tables_cursor
select @tableprevname=''
FETCH NEXT FROM tables_cursor INTO @tablename, @tableid
WHILE @@FETCH_STATUS=0
BEGIN
if
(right(@tablename, 6) like '20__0_' or right(@tablename, 6) like '20__1_'
or right(@tablename, 6) like '0_20__' or right(@tablename, 6) like '1_20__')
and len(@tablename)=len(@tableprevname)
and left(@tablename, len(@tablename)-6) like left(@tablename, len(@tableprevname)-6)
begin
print @tablename+' **log table**'
end
else
begin
print '---------'
set @descr=CONVERT(varchar(200), (SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user','dbo','table',@tablename,null, null)))
if @descr is not null and @descr not like '' set @descr=' - '+@descr else set @descr=' '
print @tablename+@descr

open col_cursor
FETCH NEXT FROM col_cursor INTO @colname, @colid, @colisnullable
WHILE @@FETCH_STATUS=0
BEGIN
if @colid=@tableid begin
set @descr=CONVERT(varchar(200), (SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user','dbo','table',@tablename,'column', @colname)))
if @descr is not null and @descr not like '' set @descr=' - '+@descr else set @descr=' '
print ' '+@colname+ case when @colisnullable=0 then ' *'+@descr else @descr end
end
FETCH NEXT FROM col_cursor INTO @colname, @colid, @colisnullable
END
close col_cursor
end

set @tableprevname=@tablename
FETCH NEXT FROM tables_cursor INTO @tablename, @tableid
END
DEALLOCATE tables_cursor
DEALLOCATE col_cursor


и дополнение - 30 топовых записей:


DECLARE @ttid int
DECLARE @ttpt int
DECLARE @tttitle varchar(100)

if exists(select * from sysobjects where name='[xXx Здесь название таблицы xXx]')
begin
print ''
print ''
print '*********'
print '--------------'
print '[xXx Здесь название таблицы xXx]: '
print '--------------'
DECLARE t_cursor CURSOR
FOR
SELECT top 30 tt.id, tt.pt, tt.title FROM [xXx Здесь название таблицы xXx] tt order by tt.id
OPEN t_cursor
FETCH NEXT FROM t_cursor INTO @ttid, @ttpt, @tttitle
WHILE @@FETCH_STATUS=0
BEGIN
print @tttitle
print ' id='+convert(varchar, @ttid)+' pt='+convert(varchar, @ttpt)
FETCH NEXT FROM t_cursor INTO @ttid, @ttpt, @tttitle
END
CLOSE t_cursor
DEALLOCATE t_cursor
select @ttid=count(*) from [xXx Здесь название таблицы xXx]
if @ttid>30
print '... ('+convert(varchar, @ttid)+' rows total)'
end