August 25, 2013 at 9:46 am
Hi,
the following Script builds a perfect SQL-Statement to Truncate multiple Tables.
Print shows the Script, but EXECUTE sp_executesql @sql doesn't exec the Script. What's wrong? I want to truncate each Table with GV%.
Regards
Nicole 😉
DECLARE @sql NVARCHAR(MAX) ;
DECLARE @TableNameStartsWith AS NVARCHAR(50)= 'GV'
SET @sql = 'SELECT ''Truncate table '' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ''' + '%' + @TableNameStartsWith
+ '%' + ''''
PRINT @sql
EXECUTE sp_executesql @sql
August 25, 2013 at 10:37 am
info 58414 (8/25/2013)
Hi,the following Script builds a perfect SQL-Statement to Truncate multiple Tables.
Print shows the Script, but EXECUTE sp_executesql @sql doesn't exec the Script. What's wrong? I want to truncate each Table with GV%.
Regards
Nicole 😉
DECLARE @sql NVARCHAR(MAX) ;
DECLARE @TableNameStartsWith AS NVARCHAR(50)= 'GV'
SET @sql = 'SELECT ''Truncate table '' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ''' + '%' + @TableNameStartsWith
+ '%' + ''''
PRINT @sql
EXECUTE sp_executesql @sql
Do any of your tables end with GV in their name?
August 25, 2013 at 10:48 am
What does it print out?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2013 at 4:24 pm
More than one thing is wrong. Your dynamic SQL runs a query against INFORMATION_SCHEMA.TABLES - but there is no need for that query to be dynamic. Presumably you want a run a query to get the SQL statements to truncate the tables.
Furthermore, your variable is called @TableNameStartsWith, but you search for all tables with GV anywhere in the name.
Here is a batch that does what seem to be asking for. The first query creates a concatenate list of TRUNCATE TABLE statements with help of FOR XML. The syntax is opaque to say the least, but it is very useful.
DECLARE @sql NVARCHAR(MAX) ;
DECLARE @TableNameStartsWith AS NVARCHAR(50)= 'GV'
SET @sql = (SELECT 'TRUNCATE TABLE ' + quotename(TABLE_NAME) + char(13) + char(10)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE @TableNameStartsWith + '%'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
PRINT @sql
EXECUTE sp_executesql @sql
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 26, 2013 at 7:30 am
A little easier to understand:
DECLARE @SQL NVARCHAR(MAX) ;
DECLARE @TableNameStartsWith AS NVARCHAR(50)= 'junk1'
SET @SQL = ''
SELECT @SQL = @SQL + ' Truncate table ' + QUOTENAME(TABLE_NAME) + '; ' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE @TableNameStartsWith + '%'
PRINT @SQL
EXECUTE sp_executesql @SQL
August 26, 2013 at 7:37 am
R. Brush (8/26/2013)
SET @SQL = ''SELECT @SQL = @SQL + ' Truncate table ' + QUOTENAME(TABLE_NAME) + '; ' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE @TableNameStartsWith + '%'
Easier to understand - at least as long as it produces what you expect. For which there are no guarantees. The correct behaviour of the above is undefined. The syntax with FOR XML is certainly crypitc, but it's safe.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 26, 2013 at 7:47 am
I would certainly validate the generated script before running it in either case.
August 27, 2013 at 7:19 am
Erland Sommarskog (8/26/2013)
R. Brush (8/26/2013)
SET @SQL = ''SELECT @SQL = @SQL + ' Truncate table ' + QUOTENAME(TABLE_NAME) + '; ' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE @TableNameStartsWith + '%'
Easier to understand - at least as long as it produces what you expect. For which there are no guarantees. The correct behaviour of the above is undefined. The syntax with FOR XML is certainly crypitc, but it's safe.
Erland, would you expand more on why the behavior of the above statement is undefined? Is it because it uses the QUOTENAME function?
The reason I ask is because that syntaxt is cited pretty frequently as a method to create a comma-separated list of values.
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
August 27, 2013 at 3:47 pm
ChrisCarsonSQL (8/27/2013)
Erland, would you expand more on why the behavior of the above statement is undefined? Is it because it uses the QUOTENAME function?
No, it is because it uses @x = @x + col. The correct result of that operation is undefined.
See further this KB Article. And while the article goes around and says that if you do this and this, it may work, I think what you really should pay attention to is the first sentence under Cause.
Yes, it usually works as expected, but occasionally I see people who ask because it didn't. There is no reason to play with fire when there is a method which is guaranteed to work; nevermind that the syntax is bulky and non-intuitive.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 28, 2013 at 7:22 am
FYI
The referenced KB Article is about aggregate concatenation queries with an ORDER BY clause, as follows:
"You may encounter unexpected results when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries."
I'm unclear as to how it relates to ChrisCarsonSQL's question.
August 28, 2013 at 8:06 am
R. Brush, I think I understand how the KB article that Erland is citing applies here.
I do agree with you that this specific query doesn't use an ORDER BY clause that contains a function. So, it should work.
My understanding of Erland's point is that even though this specific query works, this styleof query can cause problems because of the potential for inconsistent results.
If you Google something like "concatenate row values into a comma separated list" there are many references to SQL syntax that looks just like the query we have been discussing. I could easily write a query using this style that includes an ORDER BY on the result of a column function.
And, when I did that, the query wouldn't work and I'd probably be creating a new thread here 🙂
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
August 28, 2013 at 8:29 am
One additional link that refers to the Microsoft KB article:
_____________________________
Past performance != future results.
All opinions and suggestions are my own, unless they're really good. Then I most likely read them here...
August 28, 2013 at 8:40 am
ChrisCarsonSQL,
No disagreement. Point well taken...Thanks.
By the way, I recommend Erland Sommarskog's article: The Curse and Blessings of Dynamic SQL.
August 28, 2013 at 2:21 pm
R. Brush (8/28/2013)
I'm unclear as to how it relates to ChrisCarsonSQL's question.
The correct behavior for an aggregate concatenation query is undefined.
That is, the correct result of the query that Chris posted is not defined. That is, there are no guarantees that you get what you expect.
Yes, the rest of the article goes at length to say when it may work anyway, but the fírst sentence is warning sign big enough.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply