Query system tables

[pheffley]

I need to produce a list of tables by database for a particular server from Enterprise Manager. How can this be done? I've seen syscolumns in the master database, but no systables...

Thanks,

Paul

 

[nigelrivett]

sysobjects holds the tables

select * from sysobjects wherextype = 'U'

or better.

select * from information_schema.tables where table_type = 'base_table'

You will need to do this for each database.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[pheffley]

I actually need something that can seach the entire server using a where clause. Someone found this script for me:

Create table #DB_Name (dbname varchar (100))
Create table #results (dbname varchar (100), tabName varchar (100), colName varchar (100))

Insert into #DB_Name(dbname)
select [name] from master.dbo.sysdatabases where dbid > 6

DECLARE @dbName varchar (100), @sql varchar (1000)
set @dbName = ''
DECLARE db_Update CURSOR
FOR
SELECT dbname from #DB_name
OPEN db_Update
FETCH NEXT FROM db_Update INTO @dbName

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
set @sql = 'use ['+@dbname+ '] '

set @sql = @sql +' Insert into #results(dbName, TabName, colName) SELECT '
set @sql = @sql + ''''+@dbname+''''+ ' as dbName,table_name, column_name
FROM Information_Schema.Columns'
Exec (@sql)
END
FETCH NEXT FROM db_Update INTO @dbName
END
CLOSE db_Update
DEALLOCATE db_Update

Select * from #results
where tabName < like/in value set of you choice >
drop table #db_name
drop table #results