SQL Server: delete all rows of all tables to empty a database

In SQL Server it's possible to empty a database deleting all rows of all tables with the following simple script I've found in the blog post T-SQL Trick for Deleting All Data in Your Database:

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? else TRUNCATE TABLE ? '
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

But if the database contains identity columns and you want to reset them, you can visit the forum topic Truncate All Tables, answer of 06/13/2007 at 11:38:52, or download directly the code from the file EmptyAllTables.sql.txt.

SQL Server: how create a linked server to Oracle

The steps to create a new linked server to access Oracle data from SQL Server are:

  1. install the Oracle client drivers on the SQL Server machine;
  2. install the Oracle client ODAC (Oracle Data Access Components);
  3. in the linked server providers, configure the OraOLEDB.Oracle provider to allow Inprocess.

Now you can choose:

Finally: you have to configure the security, i.e. the login used to access the Oracle server.

About the two final steps: you can use the SQL Server Management Studio (as described in the first link) or via stored procedures (as in the second link).

SQL Server: How enable database mail

To enable database mail, from Sql Server Management Studio execute the following query:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE WITH OVERRIDE
GO

Then from the Management node, right-click Database Mail, and select Configure Database Mail.

Here you can add profiles, and for each profile multiple email accounts.

Remember: Database Mail requires the Sql Server Agent service to run, else messages will accumulate in a query in the Msdb database.

How search a string in all the tables of a database

If you receive a database in witch you need to search for some info, and you know the info is there, but you don't know where, probabily adding a search stored procedure could help you.

I've found a good stored procedure in the article How to search all columns of all tables in a database for a keyword?: it's written for SQL Server 7 and 2000, but I've used in SQL Server 2008 without any problem.

In the file SearchAllTables.sql.txt you can also find a backup copy of this important script!

How enable the filestream feature in SQL Server 2008

SQL Server 2008 introduces the concept of filestream, but by default it is disabled.

To enable it you have to do the following things:

  1. enable the feature from SQL Server Configuration manager (SQL Server Services, SQL Server, Properties, FileStream tab);
  2. then activate on the database server, via SSMS (Database server, properties) or via T-SQL:
         EXEC sp_configure filestream_access_level, 2
         GO
         RECONFIGURE
         GO

More details in the article How to: Enable FILESTREAM.