Multiple Database Information_schema.columns view 1

[normm]

Hi all,

I need to create a view of Information_schema.columns for a particular set of databases, all located on the same server.

The project is to improve the data documentation manuals that we currently hold as word documents, I Intend to create a database that contains a view listing all of the technical information about the others on the same server. Some databases on the server are not relevant to this process so I need a way to exclude them from the view.

Is the best way to do this to record the database names in a table within my documentation database? If so, how would I write a query that builds the view based on the database names held in this table??

Any help gratefully received.
Thanks
Lee

 

[bborissov]

To get database names:

SELECT * FROM sys.databases-- or sys.sysdatabases dependin of SQL Server version
WHERE ????

But instead of View you should create a Stored Procdedure that could loop through this Table and collect all INFORMATION_SCHEMA data from all DBs you need.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[normm]

Hi bborissov,

I know the names of the databases, that isn't the problem. Are you suggesting that I use a stored procedure to retrieve the data from Information_schema.columns by building a string?

i.e.

exec 'select * from ' + @db_name + '.Information_schema.columns'

 

[bborissov]

Yep, AFAIK there is no other way to get the schema of other databases but using Dynamic SQL.
I hope I'm wrong.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[normm]

To follow on from my last statement I have just got this working

declare @db_name varchar(max)
select @db_name = top1 dbName from dbases
declare @q varchar(max)
set @q = 'select * from ' + @db_name + '.[dbo].infoSchema'
exec(@q)

infoschema definition

create view infoSchema as
select * from Information_schema.columns
where TABLE_NAME not in ('sysdiagrams','infoSchema')

infoschema is a simple view on each of my databases as I couldn't get the following to execute from another database

SELECT     *
FROM  [PICA_DIAB_REG_Experement].[dbo].Information_schema.columns

this does what I want for one table and could be put into a cursor for more - more to follow as I figure out what I'm doing if anyone has any other suggestions on how to achieve the same thing then please let me know

lee.

 

[bborissov]

This is wrong:

SELECT     *
FROM  [PICA_DIAB_REG_Experement][COLOR=red][b].[dbo][/b][/color].Information_schema.columns

It should be:

SELECT     *
FROM  [PICA_DIAB_REG_Experement].Information_schema.columns

INFORMATION_SCHEMA is the schema

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.