By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Loop through all databases

P: 66
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
Mar 17 '09 #1
Share this Question
Share on Google+
1 Reply


P: 15
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.

Expand|Select|Wrap|Line Numbers
  1. SET NOCOUNT ON
  2.  
  3. DECLARE @Return int
  4. DECLARE @Retain int
  5. DECLARE @Status int
  6. DECLARE @PCIntra varchar(100)
  7. DECLARE @PCUltra bit 
  8.  
  9. set @PCIntra = '%'
  10. set @PCUltra = 1
  11.  
  12. SET @Status = 0
  13.  
  14. DECLARE @TPre varchar(10)
  15.  
  16. DECLARE @TDo3 tinyint
  17. DECLARE @TDo4 tinyint
  18.  
  19. SET @TPre = ''
  20.  
  21. SET @TDo3 = LEN(@TPre)
  22. SET @TDo4 = LEN(@TPre) + 1
  23.  
  24. CREATE TABLE #DBAH (TName varchar(100),
  25.                     CName varchar(100),
  26.                     CList smallint,
  27.                     CKind varchar(20),
  28.                     CSize int,
  29.                     CWide smallint,
  30.                     CMore smallint)
  31.  
  32. INSERT #DBAH
  33. SELECT O.name
  34.      , C.name
  35.      , C.colid
  36.      , T.name
  37.      , C.length
  38.      , C.prec
  39.      , C.scale
  40.   FROM sysobjects AS O
  41.   JOIN syscolumns AS C
  42.     ON O.id = C.id
  43.   JOIN systypes AS T
  44.     ON C.xusertype = T.xusertype
  45.  WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0
  46.    AND RTRIM(O.type) = 'U'
  47.    AND LEFT(O.name,@TDo3) = @TPre
  48.    AND O.name NOT LIKE 'adt%'
  49.    AND O.name NOT LIKE '%dtproper%'
  50.    AND O.name NOT LIKE 'dt[_]%'
  51.  
  52.       AND (@PCIntra IS NULL OR               C.name     LIKE @PCIntra)
  53.  
  54. SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain
  55.  
  56. 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
  57.  
  58. DROP TABLE #DBAH
  59.  
  60. SET NOCOUNT OFF
  61.  
  62. SELECT (@Status)
  63.  
  64. GO
Mar 17 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.