sql code question

In this code, will the print just display the contents of @cmd or will the @cmd be executed?

declare @table nvarchar(128)
declare @cmd nvarchar(500)
declare AllTables cursor for
select name from sys.tables
   where create_date >='2013-01-08'
open AllTables
fetch next from AllTables into @table
while @@fetch_status = 0
begin
  set @cmd = N'exec sp_rename ' + @table + ',' + 'XLElib.' + @table
  print @cmd
  fetch next from AllTables into @table
end
qbjgqbjgConsultantAsked:
Who is Participating?
 
RehanYousafCommented:
try this for schema change
DECLARE @SQL VARCHAR(MAX)
DECLARE @Change VARCHAR(MAX)

set @Change = 'ALTER SCHEMA TestSchema XLElib dbo.['

SELECT
	 @SQL = COALESCE(@SQL + @Change + [name] + ']; ', @Change + [name] + ']; ')
FROM
	sys.tables
WHERE
	create_date >= '2011-01-08'

PRINT @SQL

EXEC (@SQL)  

Open in new window

0
 
RehanYousafCommented:
to execute you need

EXEC (@cmd)

for print
PRINT @cmd
0
 
RehanYousafCommented:
instead of cursor why dont you try this

--CREATE SCHEMA TestSchema
--USE Test01
--GO

DECLARE @SQL VARCHAR(MAX)
DECLARE @Change VARCHAR(MAX)

set @Change = 'ALTER SCHEMA TestSchema TRANSFER dbo.['

SELECT
	 @SQL = COALESCE(@SQL + @Change + [name] + '] ', @Change + [name] + '] ')
FROM
	sys.tables
--WHERE
	--create_date = Your condition

PRINT @SQL

EXEC (@SQL)  

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
qbjgqbjgConsultantAuthor Commented:
I don't quite understand what your code is doing?
0
 
RehanYousafCommented:
do you want to change schema of table or just table name?
0
 
qbjgqbjgConsultantAuthor Commented:
Either will work.
0
 
qbjgqbjgConsultantAuthor Commented:
Thanks, I'll try it.
0
 
qbjgqbjgConsultantAuthor Commented:
It looks like this will work. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.