November 18, 2008 at 5:38 pm
Query to list all tables for a server along with the associated databases
Thanks
JP
November 18, 2008 at 6:32 pm
Try this.
Create Table #Tmp (dbname sysname, tablename sysname)
go
sp_msforeachdb 'use ?; Insert into #tmp select ''?'' dbname, name from sys.tables'
Select * from #Tmp
Drop Table #Tmp
November 18, 2008 at 7:00 pm
Thanks a lot
I googled it and found the same :).
But do have any idea to use something like
use @DBNAME
go
select name from sys.Tables
November 18, 2008 at 7:45 pm
November 18, 2008 at 9:47 pm
create table tablelist (DBName varchar(1000),TableName varchar(4000))
GO
DECLARE @sql varchar(8000)
DECLARE @BAK_PATH VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select name from sys.databases
OPEN c_bak
FETCH NEXT FROM c_bak INTO @BAK_PATH
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'insert into tablelist select '''+@BAK_PATH+''',name from '+@BAK_PATH +'.sys.sysobjects where xtype =''U'''
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @BAK_PATH
END
CLOSE C_BAK
DEALLOCATE C_BAK
November 19, 2008 at 12:08 pm
Thanks a lot..:)
Can you tell me like how can i pass database as a variable
i.e when i am do the following, i get this error Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@db_name".
create procedure sp_select_db_tables
@db_name varchar(50)
as
Create Table #Tmp (dbname sysname, tablename sysname)
go
sp_msforeachdb 'use ?; Insert into #tmp select ''?'' dbname, name from sys.tables'
Select * from #Tmp where dbname = @db_name
Drop Table #Tmp
go
November 20, 2008 at 9:22 am
This is one of the few places where I use cursors...
Here is a few excerpts from a change monitoring sp I wrote (it looks at more than just tables):
DECLARE @DBName VARCHAR(500)
DECLARE @sql VARCHAR(MAX)
DECLARE DBLoop CURSOR FOR
SELECT DEV.name
FROM master.sys.Databases DEV
...
OPEN DBLoop
FETCH NEXT FROM DBLoop INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Insert Dev Server DB Objects
--------------------------------------------------
SET @sql = 'USE [' + @DBName + ']
INSERT INTO [SERVER].dbo.tDatabaseObjects
SELECT ...
''' + @DBName + '''
, S.name AS SchemaName
, O.name
, O.object_id
, O.type
, O.create_date
, O.modify_date
, NULL
, NULL
FROM ' + @DBName + '.sys.Objects O
LEFT JOIN ' + @DBName + '.sys.Schemas S
ON O.[schema_id] = S.[schema_id]
WHERE type NOT IN (''C'' /* CHECK constraint */
, ''D'' /* DEFAULT (constraint or stand-alone) */
, ''F'' /* FOREIGN KEY constraint */
, ''PK'' /* PRIMARY KEY constraint */
, ''PC'' /* Assembly (CLR) stored procedure */
, ''RF'' /* Replication-filter-procedure */
, ''S'' /* System base table */
, ''TA'' /* Assembly (CLR) DML trigger */
, ''TR'' /* SQL DML trigger */
, ''UQ'' /* UNIQUE constraint */
, ''X'' /* Extended stored procedure */
, ''IT'' /* Internal table */)'
EXEC (@SQL)
--------------------------------------------------
...
FETCH NEXT FROM DBLoop INTO @DBName
END
CLOSE DBLoop
DEALLOCATE DBLoop
...
November 20, 2008 at 1:59 pm
Thanks a lot
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply