Monday, June 1, 2009

list All the tables Existsed from all the SQL Databases

This Query Helps to list All the tables Existsed from all the SQL Databases

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

The Following the original code of System Procedure "sp_MSforeachdb"


* The following table definition will be created by SQLDMO at start of each connection.

* We don't create it here temporarily because we need it in Exec() or upgrade won't work.


create proc sys.sp_MSforeachdb

@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,

@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null


set deadlock_priority low

/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

/* Preprocessor won't replace within quotes so have to use str(). */

declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)

select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))

select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))

select @dbinaccessible = N'0x80000000' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */

if (@precommand is not null)


declare @origdb nvarchar(128)

select @origdb = db_name()

/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */

/* Create the select */

exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +

N' where (d.status & ' + @inaccessible + N' = 0)' +

N' and (DATABASEPROPERTY(, ''issingleuser'') = 0 and (has_dbaccess( = 1))' )

declare @retval int

select @retval = @@error

if (@retval = 0)

exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1

if (@retval = 0 and @postcommand is not null)


declare @tempdb nvarchar(258)

SELECT @tempdb = REPLACE(@origdb, N']', N']]')

exec (N'use ' + N'[' + @tempdb + N']')

return @retval

Hope it Helps to Everyone, Please leave your valuable comments

No comments:

Post a Comment