Hello,
Trying to use the undocumented / unsupported SP
sp_MSforeachdb and have encountered a small problem.
Here is what i am trying to run...
EXEC sp_MSforeachdb @.command1="GET_ALL_USER_OBJECTS '?'"
The guts of GET_ALL_USER_OBJETS are:
**********
CREATE PROCEDURE GET_ALL_USER_OBJECTS
@.db_name sysname
AS
set @.db_name = db_name()
select db_name(), o.name as table_name, c.name as
column_name, t.name as Data_type, c.length as length,
c.xprec as prec, c.xscale as scale from syscolumns c,
sysobjects o , systypes t where o.id =c.id
and t.xtype= c.xtype
and o.xtype = 'U' --table name and column name
*****
The procedure works but only in the master DB...
Its looks like it never switches to the next DB?
Any help would be appreciated!!!
thanks all!
Lou
Could you use a cursor to iterate through the databases. I use the
following code to query the databases and put the data into a temporary
table and then, when the cursor has finished interating, then query the
temporary table.
-- CREATE CURSOR FOR IDENTIFYING DATABASE NAMES FROM
-- THE [MASTER].DBO.[SYSDATABASES] TABLE
DECLARE cDataBases CURSOR
FOR
SELECT name from master.dbo.sysdatabases
WHERE name like 'ABC%' -- you may want to filter some database names
FOR READ ONLY
-- Open the database
OPEN cDataBases
-- GET THE FIRST DB NAME
FETCH NEXT FROM cDataBases
INTO @.sDataBase
-- LOOP THROUGH THE CURSOR WHILST DB NAMES ARE RETURNED
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- THERE ARE SOME DATABASES THAT WE DON'T WANT TO QUERY
IF @.sDataBase NOT IN ('myDBa', 'myDBb')
BEGIN
-- the following is an example of some SQL that I use...change it for
your query!!
SET @.sqlStatement = N'SELECT TOP 1 ''' + @.sDataBase +
''', description, fileSize, dateStarted, dateFinished FROM ' +
@.sDataBase + '.dbo.dataUpdates WHERE Description = ''' + @.uploadType +
''' AND dateStarted>CONVERT(DATETIME, ''' + @.dateStart + ''',
102) AND dateFinished<CONVERT(DATETIME, ''' + @.dateEnd + ''', 102) ' +
' ORDER BY dateStarted DESC'
-- TOGGLE THE REM STATEMENT ON THE FOLLOWING TWO LINES TO CHECK
--select @.sqlStatement
INSERT #T_Uploads EXECUTE sp_executesql @.sqlStatement -- I INSERT
INTO A TEMPORARY TABLE AND THEN QUERY AT THE END
END
-- GET THE NEXT DATABASE NAME FROM THE CURSOR
FETCH NEXT FROM cDataBases
INTO @.sDataBase
END
-- TRASH THE CURSOR
CLOSE cDataBases
DEALLOCATE cDataBases
--THEN QUERY THE TEMPORARY TABLE (AND DROP IT AFTERWARDS)
|||You can execute the script directly with a USE statemement rather than
executing a proc in order to access the catalog in the specified database.
Example below. Personally, as I would use a cursor script similar to
Griff's but with a cursor over INFORMATION_SCHEMA.SCHEMATA.
EXEC sp_msforeachdb
'
USE ?
select db_name(), o.name as table_name, c.name as
column_name, t.name as Data_type, c.length as length,
c.xprec as prec, c.xscale as scale from syscolumns c,
sysobjects o , systypes t where o.id =c.id
and t.xtype= c.xtype
and o.xtype = ''U'' --table name and column name
'
Hope this helps.
Dan Guzman
SQL Server MVP
"Louis" <ljones@.qcsinet.com> wrote in message
news:078e01c4900f$92aeb990$a401280a@.phx.gbl...
> Hello,
> Trying to use the undocumented / unsupported SP
> sp_MSforeachdb and have encountered a small problem.
> Here is what i am trying to run...
> EXEC sp_MSforeachdb @.command1="GET_ALL_USER_OBJECTS '?'"
> The guts of GET_ALL_USER_OBJETS are:
>
> **********
> CREATE PROCEDURE GET_ALL_USER_OBJECTS
> @.db_name sysname
> AS
> set @.db_name = db_name()
> select db_name(), o.name as table_name, c.name as
> column_name, t.name as Data_type, c.length as length,
> c.xprec as prec, c.xscale as scale from syscolumns c,
> sysobjects o , systypes t where o.id =c.id
> and t.xtype= c.xtype
> and o.xtype = 'U' --table name and column name
> *****
>
> The procedure works but only in the master DB...
> Its looks like it never switches to the next DB?
> Any help would be appreciated!!!
> thanks all!
> Lou
>
|||exec sp_MSForEachDB
"
use ?;
select
db_name(),
o.name as table_name,
c.name as column_name,
t.name as Data_type,
c.length as length,
c.xprec as prec,
c.xscale as scale
from
syscolumns c,
sysobjects o ,
systypes t
where
o.id =c.id and
t.xtype= c.xtype and
o.xtype = 'U'
"
"Louis" <ljones@.qcsinet.com> wrote in message
news:078e01c4900f$92aeb990$a401280a@.phx.gbl...
> Hello,
> Trying to use the undocumented / unsupported SP
> sp_MSforeachdb and have encountered a small problem.
> Here is what i am trying to run...
> EXEC sp_MSforeachdb @.command1="GET_ALL_USER_OBJECTS '?'"
> The guts of GET_ALL_USER_OBJETS are:
>
> **********
> CREATE PROCEDURE GET_ALL_USER_OBJECTS
> @.db_name sysname
> AS
> set @.db_name = db_name()
> select db_name(), o.name as table_name, c.name as
> column_name, t.name as Data_type, c.length as length,
> c.xprec as prec, c.xscale as scale from syscolumns c,
> sysobjects o , systypes t where o.id =c.id
> and t.xtype= c.xtype
> and o.xtype = 'U' --table name and column name
> *****
>
> The procedure works but only in the master DB...
> Its looks like it never switches to the next DB?
> Any help would be appreciated!!!
> thanks all!
> Lou
>
