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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-10-29 : 08:21:25
|
| FreddyB writes "Hello. I'm working with SQL Server 7.0.I need to ontain a query that:shows the Columns names for each Databases in the server,its rows number and the name of the database which caintain the column.how Can I do That?I've used INFORMATION_SCHEMA, but it only works in a single Database.Exists one View that contains this datas?or, does anybody knows how change the database without use the sentences "USE"?... whe I use "USE"; the name of the database most be written, but when I pass like a parameter a string... shows an error...I only want to know how change from database to database (each one) if I have the name of the Database in a String...Thanks for your answer" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-29 : 08:36:22
|
Well, databases do not contain columns, they contain TABLES that contain columns. Listing columns without the table they belong to is pretty meaningless.You can use an undocumented system procedure called sp_MSforeachdb to enumerate through all of your databases and execute a command against each. This lets you access each DB's INFORMATION_SCHEMA views. Something like this:EXEC sp_MSforeachdb 'SELECT * FROM  .INFORMATION_SCHEMA.COLUMNS'The ? represents each database's name. This will go through each database and list all the columns. If you want rows for all tables:EXEC sp_MSforeachdb 'SELECT o.name, i.rows FROM ..sysobjects o INNER JOIN ..sysindexes i on o.id=i.id WHERE i.indid<2 and o.id>100'The rows column in sysindexes is only accurate if the usage information is up-to-date, you can run DBCC UPDATEUSAGE on each database to ensure that it is (warning: it could take a while to run on large databases, do NOT run it during normal or peak activity) |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-10-29 : 21:51:36
|
| Dammit, I didn't realize would make an emoticon. In the real code, that should be "[ ? ]" without any spaces or quotation marks. |
|
|
|
|
|
|
Metadata Function Needs