Performing commands on all tables in SQL Server database using the sp_MSforeachtable store procedure

From time to time I have a need to do an operation such as dropping all records or disabling all triggers for all tables in an database. Scripts using cursors can be used here, but an undocumented stored procedure sp_MSforeachtable residing in the SQL Server master DB can also be used.

Below are a couple of different uses cases for this sproc from disabling all triggers, updating statistics, checking row counts etc. Note, this stored procedure is undocumented so could be dropped or modified anytime so best not to rely on it in any of your production scripts but I’ve found it very handy in adhoc situations like prepping a DB for UAT. If you’ve got other examples of use, please send them on.

Disable all triggers
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

Enable all triggers
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

Disable all constraints
exec sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT all’

Enable all constraints
exec sp_msforeachtable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all’

Delete records in all tables
exec sp_MSForEachTable ‘DELETE FROM ?’

Truncate all tables (won’t work if any FKs)
exec sp_MSForEachTable ‘TRUNCATE TABLE ?’

Rows, size, space info for all tables
exec sp_MSforeachtable ‘exec sp_spaceused [?]’

Print all table names
exec sp_MSforeachtable ‘print “?”‘

Update statistics
exec sp_msforeachtable ‘UPDATE STATISTICS ?’

Defragments all indexes
exec sp_msforeachtable ‘dbcc indexdefrag(0, “?”)’

Reseed all tables
exec sp_msforeachtable ‘DBCC CHECKIDENT (“?”, RESEED, 0)’

Reindex all tables
exec sp_msforeachtable ‘dbcc dbreindex(“?”)’

Update a particular column. Column MUST EXIST ON ALL TABLES, otherwise execution will stop upon coming to a table without the column without a rollback.
exec sp_MSforeachtable ‘UPDATE ? SET Updated_by = ”DAVID”’

Add a column to all tables. If column already exists execution will continue.
exec sp_msforeachtable ‘alter table ? add flag bit not null default 0’;

Remove column for all tables. If column doesn’t exist execution will continue. Delete dependencies first.
exec sp_msforeachtable ‘alter table ? drop column [flag]’

Row count for all tables
create table #rowcount (tbl_name varchar(100), row_count int)
exec sp_MSforeachtable ‘insert #rowcount select ”?”,count(*) row_count from ?’
select * from #rowcount order by tbl_name
drop table #rowcount

Number of rows updated since a certain date. Assumes all tables have an auditing date column on it. Tables which don’t have the column will not cause execution to stop.

create table #updateddaterows (tbl_name varchar(100), row_count int)
exec sp_MSforeachtable ‘insert #updateddaterows select ”?”,count(*) from ? where Updated_date > ”20160101” having count(*) > 0’
select * from #updateddaterows order by tbl_name
drop table #updateddaterows

 

Number of rows updated by a user since a certain date.
create table #updatedbyrows (tbl_name varchar(100), row_count int)
exec sp_MSforeachtable ‘insert #updatedbyrows select ”?”,count(*) from ? where Updated_by = ”12345” and Updated_date > ”20160101” having count(*) > 0’
select * from #updatedbyrows order by tbl_name
drop table #updatedbyrows

Related Links

Article looking in detail at the parameters sp_msforeachtable takes

Config files specified in SQL Agent being overridden by design time package configuration

In work, our DEV and our UAT DB environments reside on the same box, but with their own SQL instances. We prefer to store SSIS packages on disk as they are a little (actually a lot) easier to manage that way. Since our DBs are on the same box we could, when configuring SSIS packages just copy the packages to two separate file locations for DEV and UAT and alter the design time package configuration to point to DEV.dtsconfig for one and UAT.dtsconfig for the other.

Rather than this however we attempted to just have the SSIS package in one location and override the default .dtsconfig file which was defined at design time by passing in the the environment specific .dtsconfig as part of the two (DEV & UAT) SQL agent jobs set-up to execute the package on schedule.  This can be done from the configurations tab in the job properties.

overiding-design-time-config

 

We expected the package to take its settings from UAT.dtsConfig as that was what was defined in the job but as we found this was not the case. This is because as of SQL 2008 SSIS packages load configurations in the following order:

  1. The utility first applies the design-time configurations.
  2. The utility then applies the run-time options that you specified on the command line when you started the utility.
  3. Finally, the utility reloads and reapplies the design-time configurations.

which meant the .dtsConfig specified in design time configuration was used. According to Behaviour Changes to Integration Services Features in SQL Server 2008 R2 on the MSDN site one can use /set to change design time settings but not the location of settings.

To get the .dtsConfig specified in the job to be the effective one, we needed to disable package configurations in design time. Not delete the existing one, but just disable it. After that the config file specified in the SQL Agent job was used.

Relative path to config file in BIDS/SSIS 2008 package configurations

As we know using absolute paths in our code can complicate things from a deployment point of view, so it’s best to use relative paths were possible. In business intelligence studio 2008 however the package configuration wizard doesn’t allow you to enter relative paths when pointing to configuration files. If you attempt to type a relative path in, clicking next will replace it with the absolute path, so we appear to have a problem here.

package-configuration

There is an easy work around however, rather than using the GUI just edit the .dtsx file directly to point to the relative path. For example if in BIDS package configuration window you have entered the path as ‘c:\SSIS\active.dtsconfig’, simply change that to ‘..\SSIS\active.dtsconfig’ using a text editor and next time you open the wizard the relative path will be used.

Using MSBuild to publish a website just like in Visual Studio

Right clicking on a web project or a WCF project (others too I’m sure) in Visual Studio presents you with a publish option which when run basically drops all files needed to deploy your project into a single folder. The publish action will use any web transform files so settings included in the to be deployed web.config are settings relevant to whatever configuration was selected when you ran the publish command.

For smaller deployments this might be fine, but if you have multiple projects and/or you need to do some pre or post work (such as FTP or IIS related) your likely to want to put everything you need into a single MSBuild script so the deployment is repeatable, quicker and safer.

If you have Visual Studio 2010 installed you can get the same functionality that Visual Studio provides through the GUI via MSBuild with a snippet like following:

msbuild-publish

In this instance I’m asking MSBuild to publish a project called MSBuildExamples.Web to a folder called Production in the location where the build script is being called from. Depending on the publish options contained within MSBuildExamples.Web.csproj the files in the generated Production folder may contain raw *.cs files and *.dlls or just the *.dlls needed to run the project.

The most important part is the targets attribute which is highlighted above. This uses a target called PipelinePreDeployCopyAllFilesToOneFolder which is a Visual Studio 2010 (and 2012 presumably) target, not a built in MSBuild one. The target is imported in MSBuildExamples.Web.csproj with the following line:

msbuild-publish2

which Visual Studio will automatically add when you create either a Web or WCF project. Running the PipelinePreDeployCopyAllFilesToOneFolder task against a project in MSBuild does exactly the same thing as running publish against the same project in the Visual Studio GUI.

Can’t receive incoming remote connections to SQL Server database

I was having problems allowing some of my co-workers connect to the instance of SQL Server on my machine. Of course I was aware of the ‘Allow remote connections to this server’ option so I checked that first. It was already on, so I checked out Google to see what I could find.

After fiddling about with firewalls and ports, it turns out that I didn’t have the TCP/IP protocol enabled within the SQL Server Configuration Manager tool. This meant network based communication with any databases on my SQL Server instance was impossible.

As soon as I enabled this protocol and restarted the instance (via SQL Server Services section above) incoming connections were possible. Of course your problem may be caused by something else so check out Daniel Walzenbach’s article on MSDN: How to enable remote connections in SQL server 2008? It helped me a lot.