Loop through all databases

[anuragshrivastava64]

Hi,

I have to run a script that fetches some value (say Column1) from a particulat table (say Table 1) from all the databases in SQL Server.
Is there any way i can parameterize (USE 'DATABSENAME') Command.

Can I use something like

Declare @StudyName char(20)
Set @StudyName ='Master'
Use '@StudyName'

Please let me know any solution

[confusedfusion]

This is a code I found and modified that will give you all tables and all columns, I have used this as a building block to do many things;

a cursor sounds like what you will need if you want a result set from all of the databases at once.

SET NOCOUNT ON
 
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
DECLARE @PCIntra varchar(100)
DECLARE @PCUltra bit 
 
set @PCIntra = '%'
set @PCUltra = 1
 
SET @Status = 0
 
DECLARE @TPre varchar(10)
 
DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint
 
SET @TPre = ''
 
SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1
 
CREATE TABLE #DBAH (TName varchar(100),
                    CName varchar(100),
                    CList smallint,
                    CKind varchar(20),
                    CSize int,
                    CWide smallint,
                    CMore smallint)
 
INSERT #DBAH
SELECT O.name
     , C.name
     , C.colid
     , T.name
     , C.length
     , C.prec
     , C.scale
  FROM sysobjects AS O
  JOIN syscolumns AS C
    ON O.id = C.id
  JOIN systypes AS T
    ON C.xusertype = T.xusertype
 WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0
   AND RTRIM(O.type) = 'U'
   AND LEFT(O.name,@TDo3) = @TPre
   AND O.name NOT LIKE 'adt%'
   AND O.name NOT LIKE '%dtproper%'
   AND O.name NOT LIKE 'dt[_]%'
 
      AND (@PCIntra IS NULL OR               C.name     LIKE @PCIntra)
 
SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
 
SELECT CASE WHEN @PCUltra = 0 THEN SUBSTRING(TName,@TDo4,100) ELSE TName END AS TName, CName, CList, CKind, CSize, CWide, CMore FROM #DBAH ORDER BY TName, CList
 
DROP TABLE #DBAH
 
SET NOCOUNT OFF
 
SELECT (@Status)
 
GO