June 28, 2010 at 2:47 pm
ok, I need to add a table to every database on my server. Here is my code and the error I get when I change the @exec (print debug statement) to execute the @sql instead of just printing it out.
CODE:
DECLARE @sql VARCHAR(8000),
@exec bit
--set @exec = 0
set @exec = 1
SELECT @sql =
'CREATE TABLE [dbo].[DBVersionInfo](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Module] [varchar](100) NOT NULL,
[BuildType] [varchar](100) NOT NULL,
[Version] [numeric](4, 4) NOT NULL,
[VersionDate] [datetime] NOT NULL,
[BuildNumber] [int] NOT NULL,
[TFSChangesetNumber] [int] NOT NULL,
CONSTRAINT [PK_DBVersionInfo] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]'
IF @exec = 0
PRINT @sql
ELSE
--EXEC(@sql)
exec master.dbo.sp_msforeachdb
'USE [?]
--IF THE DATABASE IS NOT A SYSTEM DATABASE
IF DB_ID(''?'') > 4
BEGIN
--print ''wahoo''
--CREATE THE TABLE IF IT DOES NOT EXIST
IF NOT EXISTS (SELECT 1 FROM sys.sysobjects WHERE NAME = ''DBVersionInfo'')
BEGIN
--EXEC @sql
--PRINT ''TABLE CREATED IN '' + ''?''
--print ''wahoo again''
PRINT @sql
END
END'
ERROR CODE: (I get this for each db I am trying to add the table to)
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@sql".
HELP!! :w00t:
June 28, 2010 at 2:59 pm
@sql is not available inside exec master.dbo.sp_msforeachdb
You have to include all the code in the stored procedure parameter.
June 28, 2010 at 3:09 pm
I got the idea to do it this way from the following code:
DECLARE @sql VARCHAR(1000)
SET @sql = 'SELECT ''?'', so.name, MAX(si.rows)
FROM sysobjects so
JOIN sysindexes si on so.id = si.id
WHERE so.xtype = ''U''
GROUP BY so.name
ORDER BY 2 DESC'
CREATE TABLE #t ([Database] VARCHAR(255), [TableName] VARCHAR(255), [RowCount] INT)
INSERT #t
********EXEC sp_msforeachdb @sql********
SELECT * FROM #t ORDER BY 1, 3 DESC
DROP TABLE #t
June 28, 2010 at 3:29 pm
Here's how I did it with Excel. You could drop this code in a one cell, easily fill your db names in another, and create a quick script with
use dbname
go
create table ...
http://www.sqlservercentral.com/articles/DTS/datamigrationquicklyinsertingnewdata/1419/
June 28, 2010 at 3:35 pm
June 28, 2010 at 3:38 pm
My pleasure. It's a trick an admin showed me for scripting something from the command line. Add in a column for server and you have a great SQLCMD script generator.
June 28, 2010 at 4:44 pm
Steve, how does one see the code for sp_MSForeachdb stored proc????? in my code I have..."where db_ID >4......."" which takes care of 1-4 being the system tables. I want to also catch the ReportServer and ReportServerTempDB tables, which in the Sys.databases view, database_ID is # 5, 6. I would like to be able to verify that......
thank you!!
June 28, 2010 at 5:13 pm
You can view the code in master and just copy it. It's a simple cursor.
I tend to use names in this case, avoiding specific system names.
June 29, 2010 at 8:48 am
June 29, 2010 at 9:43 am
Steve....just to clarify.....(LOL)
when you query the sys.sysdatabases view, you get dbid as a column. the sp_msforeachdb proc querys this view. my code says 'if DB_ID >4' (and this seems to work)......
so my question is, if the column is dbid how is my checking DB_ID mapping to that column?? This works, and the next 2 dbs in line are indeed ReportServer and ReportServerTempDB which are 5 and 6 respectively...and that is what I want....
I just want to understand as well......
thank you!! :hehe:
June 29, 2010 at 9:46 am
I'm not sure where you are getting db_id? Is it the code above there? SQL doesn't map things. That column would have to be returned by a query.
As I mentioned, I wouldn't use db ids. You could have SSRS installed on another instance and with different database IDs. I'd map to databasename instead and exclude "model, msdb, master, tempdb, ReportServer, ReportServertempdb" with a NOT IN
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply