Stack Overflow
Stack Overflow

Questions tagged [sp-msforeachtable]

29 questions
273
votes
17 answers

How to drop all tables in a SQL Server database?

I'm trying to write a script that will completely empty a SQL Server database. This is what I have so far: USE [dbname] GO EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all' EXEC sp_msforeachtable 'DELETE ?' When I run it in the…
dixuji
  • 2,733
  • 3
  • 14
  • 4
38
votes
7 answers

Delete all views from Sql Server

By using this statement in SQL Server: EXEC sp_msforeachtable 'DROP TABLE ?' I know it's possible to delete all tables at once. Is there a similar statement for views? I tried this hoping to be lucky: EXEC sp_msforeachview 'DROP VIEW ?' but it…
Sam
  • 3,067
  • 19
  • 53
  • 55
27
votes
3 answers

How to exclude tables from sp_msforeachtable

I know that sp_msforeachtable allows to perform queries on all tables. I have 100 tables and I want to perform the same query on 97 tables. I'm using this query: EXEC sp_MSForEachTable "DELETE FROM ?" Is it possible to exclude certain tables?
user194076
  • 8,787
  • 23
  • 94
  • 154
26
votes
3 answers

SQL Server: How to make server check all its check constraints?

It seems that some scripts generated by Enterprise Manager* (or not, it doesn't matter) created check constraints WITH NOCHECK. Now when anyone modifies the table, SQL Server is stumbling across failed check constraints, and throwing errors. Can i…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
19
votes
2 answers

SQL Server sp_msforeachtable usage to select only those tables which meet some condition

I am trying to write this query to find all tables with specific column with some specific value. This is what I've done so far - EXEC sp_MSforeachtable @command1=' IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE…
Soham Dasgupta
  • 5,061
  • 24
  • 79
  • 125
16
votes
4 answers

ALTER INDEX failed because of QUOTED_IDENTIFIER when running from sp_msForEachTable

When I try to rebuild an index on a table: ALTER INDEX ALL ON [dbo].[Allocations] REBUILD that works fine. But when I call EXECUTE sp_msForEachTable 'ALTER INDEX ALL ON ? REBUILD' I reach the same same table, and it fails with: Msg 1934, Level…
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
5
votes
2 answers

How to use both sp_msforeachtable and sp_msforeachdb in the same query?

Is there any way to reference the table inside a 'sp_MSforeachtable' loop running inside a 'sp_msforeachdb' loop? For example, in the following query the '?' is always referencing the database: DECLARE @cmd VARCHAR(8000); SET @cmd = 'USE ?; EXEC…
Michael
  • 477
  • 1
  • 5
  • 12
3
votes
1 answer

How to delete data in all tables from a database except except few tables

I have 50+ tables in my database and I want to delete all the data in 48 tables. I tried using EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL' EXEC sp_MSForEachTable…
Maltesh
  • 383
  • 2
  • 6
  • 14
2
votes
0 answers

MSforeachtable does not execute triggers

I have following triggers which I want to execute on each table in my database: EXECUTE sp_MSforeachtable @command1 = 'IF EXISTS (SELECT * FROM sys.triggers WHERE Object_ID = Object_ID("Insert_Serverdate_Into_CreatedAt")) DROP…
Canox
  • 557
  • 1
  • 7
  • 20
2
votes
2 answers

sp_msforeachtable does not give me the right result

I want to use sp_msforeachtable to do some work for some tables in a database. I use the IF statement to filter the tables. But it doesn't give me the right answer. As the following script shows, I use AdventureWorks to do the testing. I want to do…
Just a learner
  • 26,690
  • 50
  • 155
  • 234
2
votes
1 answer

Getting Multipart Identifier could not be bound using sp_MSforeachtable

I'm using the sp_MSForeachtable to retrieve the columnames of all tables and concatenating the columnames in a single string. I'm using the following query. I've executed the same providing the parameter through a variable for a single table and…
Mariano Barbuscio
  • 21
  • 1
2
votes
2 answers

How to execute multiline query using sp_MSforeachtable

How do I add an index to every table using sp_MSforeachtable? It keeps giving errors. EXEC sp_MSforeachtable @precommand = 'declare @idx as char;', @command1 = ' set @idx = ''idx_'' + ? + ''_modified_on''; print @idx; IF EXISTS (SELECT * FROM…
Chloe
  • 25,162
  • 40
  • 190
  • 357
2
votes
1 answer

Copy from all tables, insert into all tables (sp_MSforeachtable alternative)

I've decided to share my experience in trying to copy data from all tables into the same name tables in a different schema. I'm sure my experience can help others seeking mass table operations without using the unsupported and frankly limited…
Luis Ferrao
  • 1,463
  • 2
  • 15
  • 30
1
vote
0 answers

What is the replacement for sp_MSforeachtable procedure

What is the replacement solution from Microsoft for the (to be deprecated) procedure sp_MSforeachtable on SQL Server newer versions?
ASPaiva
  • 143
  • 9
1
vote
1 answer

sp_msforeachtable performing actions on variables

I am trying to figure out how to use sp_msforeachtable to perform an action on all tables and variables that match variable/table names stored in another table IE I have a table that has 3 columns : table, variable, action and I am trying to use…
Adam Sanders
  • 125
  • 11
1
2