http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables (sp_MSforeachtable)

http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.

When any data modification operations (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. DBCC DBREINDEX statement can be used to rebuild all the indexes on all the tables in database. DBCC DBREINDEX is efficient over dropping and recreating indexes.

Execution of Stored Procedure sp_updatestats at the end of the Indexes process ensures updating stats of the database.
Method 1: My Preference
USE MyDatabase
GOEXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GOEXEC sp_updatestats
GO
Method 2:

USE MyDatabase
GOCREATE PROCEDURE spUtil_ReIndexDatabase_UpdateStats
AS
DECLARE @MyTable VARCHAR(255)
DECLARE myCursor
CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN myCursor
FETCH NEXT
FROM myCursor INTO @MyTable
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing Table:  ' + @MyTable
DBCC DBREINDEX(@MyTable, '', 80)
FETCH NEXT
FROM myCursor INTO @MyTable
END
CLOSE myCursor
DEALLOCATE myCursor
EXEC sp_updatestats
GO

Adventures in SQL: SP_MSFOREACHTABLE to find the largest table

by Jen Stirrup

When exploring a SQL Server database for the first time, it’s always useful to identify the most populated tables first because they are very likely to be the most important tables. To do this in a situation where there are tight time-scales, it’s possible to use some undocumented SQL Server procedures to quickly produce a list of the tables which have the most rows.

Before proceeding, however, it’s wise to note that undocumented stored procedures are not guaranteed by Microsoft. Further, they are not ‘fixed’ so they could change without warning, so use this command at your own discretion, and treat the results with care!

One example of an undocumented SQL Server procedure which has been useful is SP_FOREACHTABLE.  Although it’s not guaranteed, it might be useful for some to try it out as a starting point when exploring data, when it’s useful to find out the most populated tables in your new unexplored database.

In this example, a new table called TableRowCount will be created. This table will hold the results of the execution of SP_FOREACHTABLE.  To assist you, the SQL to do this is given below:

/****** Check if table TableRowCount exists already, and if so, delete it    ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableRowCount]') AND type in (N'U'))
DROP TABLE [dbo].[TableRowCount]
GO
/****** Create table TableRowCount                                                         ******/
CREATE TABLE [TableRowCount](
  TableName sysname,
  [TableRowCount] int )

Once the table is created, the SP_FOREACHTABLE procedure is executed as follows:

/****** Execute stored procedure, and save results in table TableRowCount ******/
EXEC sp_MSForEachTable 'INSERT [TableRowCount](TableName, [TableRowCount]) SELECT ''?'', COUNT(*) FROM ?'

Then, it’s possible to retrieve the results of this command from the TableRowCount table, as follows:

/****** Display the results from table TableRowCount, in descending order ******/


select * from TableRowCount
order by TableRowCount.TableRowCount desc

The above statement will sort the data so that the table with the highest number of rows will appear at the top. It's a guess that the most populated table is an important one, but it's not a bad guess all the same!

Empty All Tables from database:

CREATE PROCEDURE sp_EmplyAllTable

AS

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

EXEC sp_MSForEachTable ‘DELETE FROM ?’

EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

GO

SQL SERVER – Reseting Identity Values for All Tables by pinaldave

Sometime email requesting help generates more questions than the motivation to answer them. Let us go over one of the such examples. I have converted the complete email conversation to chat format for easy consumption. I almost got a headache after around 20 email exchange. I am sure if you can read it and feel my pain.

DBA: "I deleted all of the data from my database and now it contains table structure only. However, when I tried to insert new data in my tables I noticed that my identity values starts from the same number where they actually were before I deleted the data."

Pinal: "How did you delete the data?"

DBA: "Running Delete in Loop?"

Pinal: "What was the need of such need?"

DBA: "It was my development server and I needed to repopulate the database."

Pinal: "Oh so why did not you use TRUNCATE which would have reset the identity of your table to the original value when the data got deleted? This will work only if you want your database to reset to the original value. If you want to set any other value this may not work."

DBA: (silence for 2 days)

DBA: "I did not realize it. Meanwhile I regenerated every table's schema anddropped the table and re-created it."

Pinal: "Oh no, that would be extremely long and incorrect way. Very bad solution."

DBA: "I understand, should I just take backup of the database before I insert the data and when I need, I can use the original backup to restore the database. This way I will have identity beginning with 1."

Pinal: "This going totally downhill. It is wrong to do so on multiple levels. Did you even read my earlier email about TRUNCATE."

DBA: "Yeah. I found it in spam folder."

Pinal: (I decided to stay silent)

DBA: (After 2 days) "Can you provide me script to reseed identity for all of my tables to value 1 without asking further question."

Pinal:

USE DATABASE;
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 1)'GO

Our conversation ended here. If you have directly jumped to this statement, I encourage you to read the conversation one time. There is difference between reseeding identity value to 1 and reseeding it to original value - I will write an another blog post on this subject in future.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Deleting all records from all tables

sp_MsForEachTable 'TRUNCATE TABLE ?'

or

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
EXEC sp_msforeachtable 'DELETE FROM ?' 
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 

Deleting all records in case of FK And PK and for excluding some tables

http://riteshshah.wordpress.com/2009/03/04/empty-all-table-or-delete-all-data-from-all-table-by-sp_msforeachtable-in-sql-server-2005/

1. EXEC sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

exec sp_MSforeachtable
@command1 = ‘delete from ?’,
@whereand = ‘and o.name not in( ”ABC”,”XYZ”)’

EXEC sp_msforeachtable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL’

EXEC sp_msforeachtable @command1="print '?'"

Example to print all table names excluding some tables

exec sp_MSforeachtable

@command1 = "print '?'",

@whereand = " and o.name not in( 'Average7')"

Or

exec sp_MSforeachtable

@command1 = 'print "?"',

@whereand = 'and o.name not in( "Average7")'

exec sp_MSforeachtable

@command1 = ‘delete from ?',

@whereand = ‘ and o.name not in(“Average7”)’

Note - @whereand should not be greater then 128 in length