return the code that sp_MSforeachtable would produce

Question

Post reply

return the code that sp_MSforeachtable would produce

DamianC

SSCrazy Eights

Points: 8006
More actions
August 19, 2015 at 9:57 am

#310997

Hello

I have the following:

sp_MSforeachtable @command1="delete from ? where '?' like '%_st]'"

Rather than actually run it, I'm interested to see what it will do

A bit like print @sqlcommand rather than exec @sqlcommand

Is there an easy way?

Thanks

- Damian

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

djj

SSCoach

Points: 18831

August 19, 2015 at 10:27 am

#1821117

Are you looking for a list of tables? Also you may want to use QUOTENAME

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

You can use a PRINT to just print the command, then remove the print and the extra quotes when ready to run the commands.



SET QUOTED_IDENTIFIER OFF; --this is implied by how you specified the string for foreachtable

EXEC sp_MSforeachtable @command1="print 'delete from ? where PARSENAME(''?'', 1) like ''%_st'''"

--EXEC sp_MSforeachtable @command1="delete from ? where PARSENAME('?', 1) like '%_st'"

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

drew.allen SSC Guru Points: 76776 More actions · Answer 3

You can use the following:



SELECT t.TABLE_NAME, REPLACE(@cmd, '?', t.TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES t

The table name doesn't (typically) end with a ']', so it won't delete any records.

Also '_' is a wildcard, so it may not be giving you the results that you expect. So, it will match a table called 'test'. If you want it to match the character '_', you need to put it in brackets.

You may also want to use an IF statement instead of putting this in the where clause.

Finally, you may want to use TRUNCATE TABLE instead of deleting unless you are planning to add additional criteria.

Combining all of these I would suggest using the following string

'IF ''?'' LIKE ''%[_]st'' TRUNCATE TABLE ? '

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

Excellent point about the underscore, it definitely needs bracketed.

Be careful. The ? is replaced with a fully delimited schema and table, such as:

[dbo].[table1]

Thus, a comparison like:

IF ''?'' LIKE ''%[_]st''

can never match, because your string doesn't end with a bracket (that's why I think the original code had an ending bracket).

I prefer to make it clear that the table name is what's being compared, that's why I use:

PARSENAME('?', 1)

to pull out the undelimited table name only.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

drew.allen SSC Guru Points: 76776 More actions · Answer 5

ScottPletcher (8/19/2015)

Be careful. The ? is replaced with a fully delimited schema and table, such as:

[dbo].[table1]

I didn't realize that was the case. In that case my query above needs to be modified:



SELECT t.TABLE_NAME, REPLACE(@cmd, '?', QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES t

I do understand that there is some concern about the INFORMATION_SCHEMA views not returning accurate data, but I'm not usually worried about completely accurate results when I use those views.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 100262 More actions · Answer 6

drew.allen (8/19/2015)

ScottPletcher (8/19/2015)

Be careful. The ? is replaced with a fully delimited schema and table, such as:

[dbo].[table1]

I didn't realize that was the case. In that case my query above needs to be modified:

SELECT t.TABLE_NAME, REPLACE(@cmd, '?', QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES t

I do understand that there is some concern about the INFORMATION_SCHEMA views not returning accurate data, but I'm not usually worried about completely accurate results when I use those views.

Drew

I'm more worried about the I_S being slow and causing deadlocks. No matter what the underlying view definition shows, I've seen it happen far too many times to ever use I_S views in SQL Server again.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

DamianC SSCrazy Eights Points: 8006 More actions · Answer 7

Thanks all

Very useful as, collectively you gave very good pointers regarding parsename, truncate vs delete and quoted identifiers

I am still wondering what the logic behind the original ']' was

Also, when I look at the resultant statements (using print and count), it returned 0 rows in all cases

Suggesting that the statement did nothing!

- Damian