July 22, 2015 at 9:02 pm
The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement,
Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP'
This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
Thanks,
pat
July 22, 2015 at 9:08 pm
Yeah, that won't work...
This is something you can accomplish using Dynamic SQL.
-- Itzik Ben-Gan 2001
July 22, 2015 at 9:43 pm
mpdillon (7/22/2015)
The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement,
Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP'
This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
Thanks,
pat
You don't want to just delete the tables from this view, you want to actually drop the table. Since you can't do that with a query, you'll need to build some dynamic SQL to do it.
DECLARE @SQLCMD VARCHAR(MAX);
SET @SQLCMD = (
SELECT 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE LEFT(name, 5) = 'APTMP'
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)');
PRINT @SQLCMD;
-- EXECUTE (@SQLCMD);
Verify that this will drop just the tables that you want, then enable the remarked-out code to actually do it.
Note that this script does not take into consideration foreign keys, and whether the FK might need to be dropped from other tables first. Nor does it consider PK/FK relationships where one table would need to be dropped prior to the other (if both are being deleted).
This FOR XML method is discussed in this article[/url], as well as in the book linked to in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 23, 2015 at 10:03 am
Btw, the WHERE clause can be improved to allow an index seek, if applicable:
WHERE LEFT(name, 5) = 'APTMP'
should be:
WHERE name LIKE 'APTMP%'
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2015 at 12:00 pm
ScottPletcher (7/23/2015)
Btw, the WHERE clause can be improved to allow an index seek, if applicable:WHERE
LEFT(name, 5) = 'APTMP'should be:
WHERE name LIKE 'APTMP%'
Good luck with adding that index to the system catalog view there.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 23, 2015 at 12:12 pm
WayneS (7/23/2015)
ScottPletcher (7/23/2015)
Btw, the WHERE clause can be improved to allow an index seek, if applicable:WHERE
LEFT(name, 5) = 'APTMP'should be:
WHERE name LIKE 'APTMP%'
Good luck with adding that index to the system catalog view there.
And actually, to eliminate the implicit conversion, it should be:
WHERE name LIKE N'APTMP%'
July 23, 2015 at 1:21 pm
Lynn Pettis (7/23/2015)
WayneS (7/23/2015)
ScottPletcher (7/23/2015)
Btw, the WHERE clause can be improved to allow an index seek, if applicable:WHERE
LEFT(name, 5) = 'APTMP'should be:
WHERE name LIKE 'APTMP%'
Good luck with adding that index to the system catalog view there.
And actually, to eliminate the implicit conversion, it should be:
WHERE name LIKE N'APTMP%'
True, I don't control the system catalogs, but I do control where my code uses poor practices or not. And SQL may add such an index in the future for all we know.
Never specify a unicode literal unless you have to, let SQL implicitly convert literals. Otherwise, if you mistakenly code N' when you shouldn't, you'll force an implicit conversion on the table column and again reduce index usage possibilities.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2015 at 1:36 pm
ScottPletcher (7/23/2015)
Lynn Pettis (7/23/2015)
WayneS (7/23/2015)
ScottPletcher (7/23/2015)
Btw, the WHERE clause can be improved to allow an index seek, if applicable:WHERE
LEFT(name, 5) = 'APTMP'should be:
WHERE name LIKE 'APTMP%'
Good luck with adding that index to the system catalog view there.
And actually, to eliminate the implicit conversion, it should be:
WHERE name LIKE N'APTMP%'
True, I don't control the system catalogs, but I do control where my code uses poor practices or not. And SQL may add such an index in the future for all we know.
Never specify a unicode literal unless you have to, let SQL implicitly convert literals. Otherwise, if you mistakenly code N' when you shouldn't, you'll force an implicit conversion on the table column and again reduce index usage possibilities.
Never say never.
Know your data. The data type for name in sys.tables is sysname (nvarchar(128)).
July 23, 2015 at 1:58 pm
Lynn Pettis (7/23/2015)
ScottPletcher (7/23/2015)
Lynn Pettis (7/23/2015)
WayneS (7/23/2015)
ScottPletcher (7/23/2015)
Btw, the WHERE clause can be improved to allow an index seek, if applicable:WHERE
LEFT(name, 5) = 'APTMP'should be:
WHERE name LIKE 'APTMP%'
Good luck with adding that index to the system catalog view there.
And actually, to eliminate the implicit conversion, it should be:
WHERE name LIKE N'APTMP%'
True, I don't control the system catalogs, but I do control where my code uses poor practices or not. And SQL may add such an index in the future for all we know.
Never specify a unicode literal unless you have to, let SQL implicitly convert literals. Otherwise, if you mistakenly code N' when you shouldn't, you'll force an implicit conversion on the table column and again reduce index usage possibilities.
Never say never.
Know your data. The data type for name in sys.tables is sysname (nvarchar(128)).
I do say never. Never use ISNULL() in a WHERE or JOIN -- it's never needed, and it can be very harmful.
Never specify a unicode literal unless you absolutely have to. SQL will implicitly convert it to unicode it if needs to be, which is truly trivial time. But, if you add it and the underlying column data type has changed to varchar, you force an implicit conversion with all its attendant potential performance issues. I've seen some clients changing nvarchar back to varchar where they can to save disk space. For longer values in (very) big tables, that can make a significant difference in the total size of the db.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2015 at 2:05 pm
ScottPletcher (7/23/2015)
...
I do say never. Never use ISNULL() in a WHERE or JOIN -- it's never needed, and it can be very harmful.
Never specify a unicode literal unless you absolutely have to. SQL will implicitly convert it to unicode it if needs to be, which is truly trivial time. But, if you add it and the underlying column data type has changed to varchar, you force an implicit conversion with all its attendant potential performance issues. I've seen some clients changing nvarchar back to varchar where they can to save disk space. For longer values in (very) big tables, that can make a significant difference in the total size of the db.
I guess we will have to agree to disagree. I have had to fix too much code that relied on implicit conversion of literal strings where if the original developer had simply prefaced a literal string with an N there wouldn't have been a performance issue.
July 23, 2015 at 2:27 pm
Lynn Pettis (7/23/2015)
ScottPletcher (7/23/2015)
...
I do say never. Never use ISNULL() in a WHERE or JOIN -- it's never needed, and it can be very harmful.
Never specify a unicode literal unless you absolutely have to. SQL will implicitly convert it to unicode it if needs to be, which is truly trivial time. But, if you add it and the underlying column data type has changed to varchar, you force an implicit conversion with all its attendant potential performance issues. I've seen some clients changing nvarchar back to varchar where they can to save disk space. For longer values in (very) big tables, that can make a significant difference in the total size of the db.
I guess we will have to agree to disagree. I have had to fix too much code that relied on implicit conversion of literal strings where if the original developer had simply prefaced a literal string with an N there wouldn't have been a performance issue.
I'd love to see any example of that. Unicode has a higher preference, so SQL should always implicitly convert it if it's required.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2015 at 3:29 pm
ScottPletcher (7/23/2015)
Lynn Pettis (7/23/2015)
ScottPletcher (7/23/2015)
...
I do say never. Never use ISNULL() in a WHERE or JOIN -- it's never needed, and it can be very harmful.
Never specify a unicode literal unless you absolutely have to. SQL will implicitly convert it to unicode it if needs to be, which is truly trivial time. But, if you add it and the underlying column data type has changed to varchar, you force an implicit conversion with all its attendant potential performance issues. I've seen some clients changing nvarchar back to varchar where they can to save disk space. For longer values in (very) big tables, that can make a significant difference in the total size of the db.
I guess we will have to agree to disagree. I have had to fix too much code that relied on implicit conversion of literal strings where if the original developer had simply prefaced a literal string with an N there wouldn't have been a performance issue.
I'd love to see any example of that. Unicode has a higher preference, so SQL should always implicitly convert it if it's required.
Well, I would if I could but I can't. Code only exists on a classified network and happens to be 7700 miles away from where I am now.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply