---------------------

DECLARE @DbNames nvarchar(60)
SET @DbNames = 'Test3_Test2_Test1_'

DECLARE @CurrentDbName nvarchar(20)

WHILE LEN(@DbNames) > 0
BEGIN

	PRINT '>' + @DbNames + '<'

	SET @CurrentDbName = SUBSTRING(@DbNames, LEN(@DbNames) - 5 , 5)

	IF @CurrentDbName = 'Test1'
	BEGIN
		USE Test1
		PRINT 'Creating objects in database ' + @CurrentDbName
	END
	ELSE IF @CurrentDbName = 'Test2'
	BEGIN
		USE Test2
		PRINT 'Creating objects in database ' + @CurrentDbName
	END
	ELSE IF @CurrentDbName = 'Test3'
	BEGIN
		USE Test3
		PRINT 'Creating objects in database ' + @CurrentDbName
	END
	
	CREATE TABLE TestTab (TestCol int)

	SET @DbNames = SUBSTRING(@DbNames, 0, LEN(@DbNames) - 5)

END

DECLARE @DbNames nvarchar(60)
SET @DbNames = 'Test3_Test2_Test1_'

DECLARE @CurrentDbName nvarchar(20)

WHILE LEN(@DbNames) > 0
BEGIN

	PRINT '>' + @DbNames + '<'

	SET @CurrentDbName = SUBSTRING(@DbNames, LEN(@DbNames) - 5 , 5)

	if db_id(@CurrentDbName) > 0
	begin
		exec ('
		use ' + @CurrentDbName + '
	
		CREATE TABLE TestTab (TestCol int)
		')
	end

	SET @DbNames = SUBSTRING(@DbNames, 0, LEN(@DbNames) - 5)

END

		exec ('
		use ' + @CurrentDbName + '
	
		CREATE TABLE TestTab (TestCol int)
		')

		exec ('
		use ' + @CurrentDbName + '
	
		CREATE TABLE TestTab (TestCol int)
                CREATE TRIGGER ...

		')

quote:

---

Originally posted by PiecesOfEight

Thanks. At least now I know I'm not crazy . The problem I have with the EXEC approach is that I have objects I need to create that have to be in their own batch. So the following works:

		exec ('
		use ' + @CurrentDbName + '
	
		CREATE TABLE TestTab (TestCol int)
		')

But the following does not. Any ideas about working around that issue?

		exec ('
		use ' + @CurrentDbName + '
	
		CREATE TABLE TestTab (TestCol int)
                CREATE TRIGGER ...

		')

Even just a USE and the trigger creation in the same exec fails b/c the trigger creation has to be in its own batch.

---

		exec ('
		use ' + @CurrentDbName + '	
		CREATE TABLE TestTab (TestCol int)')

                exec ('
		use ' + @CurrentDbName + '
                CREATE TRIGGER ...')

quote:

---

Originally posted by jezemine

see my post of 12/12/2007 11:42:51 in this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94068

(note to regulars: how do you link to a particular post, and not the topic as a whole?)

---

elsasoft.org

---

quote:

---

Originally posted by jezemine

and how do you discover the replyid? do you have to view source on the page?

---

elsasoft.org

---

quote:

---

then do what Tara suggested.

---

DECLARE @DbNames nvarchar(60)
SET @DbNames = 'Test1_Test2_Test3_'

DECLARE @CurrentDbName nvarchar(20)

WHILE LEN(@DbNames) > 0
BEGIN

	PRINT '>' + @DbNames + '<'

	SET @CurrentDbName = SUBSTRING(@DbNames, LEN(@DbNames) - 5 , 5)

--select @CurrentDbName [@CurrentDbName]

	IF @CurrentDbName = 'Test1'
	BEGIN
		USE Test1
		PRINT 'Creating objects in database ' + @CurrentDbName
	END
	ELSE IF @CurrentDbName = 'Test2'
	BEGIN
		USE Test2
		PRINT 'Creating objects in database ' + @CurrentDbName
	END
	ELSE IF @CurrentDbName = 'Test3'
	BEGIN
		USE Test3
		PRINT 'Creating objects in database ' + @CurrentDbName
	END


--	select db_name() [db_name()]	

	exec('CREATE TABLE TestTab (TestCol int)')
	exec ('
create trigger trTestTab on TestTab after insert 
as 
begin 
	select * from inserted
end
')

	SET @DbNames = SUBSTRING(@DbNames, 0, LEN(@DbNames) - 5)

END