• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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
0
qbjgqbjg
Asked:
qbjgqbjg
  • 4
  • 4
1 Solution
 
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
 
qbjgqbjgConsultantAuthor Commented:
I don't quite understand what your code is doing?
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
RehanYousafCommented:
do you want to change schema of table or just table name?
0
 
qbjgqbjgConsultantAuthor Commented:
Either will work.
0
 
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
 
qbjgqbjgConsultantAuthor Commented:
Thanks, I'll try it.
0
 
qbjgqbjgConsultantAuthor Commented:
It looks like this will work. Thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now