all databases' name with their tables' name

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

all databases' name with their tables' name

Author

Topic

soly
Starting Member

1 Post

Posted - 2008-04-25 : 15:09:30

hi all,

i want to write SQL query that create table and insert in this table all databases' name and their tables' name that exist in my server.

e.g

DB name Table name
------- ----------

Northwind product
Northwind customers
Northwind Employees
Pubs authors
Pubs discounts
Pubs employee
msdb log_shipping_databases
msdb log_shipping_monitor

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-25 : 15:24:18

insert into yourTable(DatabaseName, TableName)
sp_msforeachdb 'select '''?''' as databaseName, table_name from ?.information_schema.tables'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-25 : 15:25:23

Something like this will work (using undocumented MS stored procedures):

CREATE PROCEDURE usp_DataRowCounts AS

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- Declare local variables
EXEC master..sp_MSForeachdb '
USE

IF DB_ID(''?'')>4
BEGIN

insert into DBMaint..DataRowCounts
SELECT o.name, i.rowcnt, getdate() as RunDate, ''?''
FROM sysobjects o, sysindexes i
WHERE i.id = o.id AND indid IN(0,1) AND xtype = ''u''

--BEGIN
--PRINT ''?''
--END
END'

GO

Table DataRowCounts looks like this:

CREATE TABLE [dbo].[DataRowCounts] (
[Table_Name] [sysname] NOT NULL ,
[Row_Count] [bigint] NOT NULL ,
[Run_Date] [datetime] NOT NULL ,
[DBName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Terry

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-28 : 03:37:42

quote:
Originally posted by spirit1

insert into yourTable(DatabaseName, TableName)
sp_msforeachdb 'select '''?''' as databaseName, table_name from ?.information_schema.tables'

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

insert into yourTable(DatabaseName, TableName)
sp_msforeachdb 'select ''?'' as databaseName, table_name from ?.information_schema.tables'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources