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 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!

Import Data from Excel with T-SQL

Here are the two examples of importing data from Excel into SQL Server using T-SQL: both tecniques use the OPENROWSET function:

--Excel 97-2003
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;IMEX=1;Database=C:\MyExcel.xls', 'SELECT * FROM [Sheet1$]')

--Excel 2007-2010
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=C:\MyExcel.xlsx', 'SELECT * FROM [Sheet1$]')

If you are unable to use the OPENROWSET function, you need to do the following:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SQL Server On Non Standard Ports

If you have an installation of SQL Server on non standard ports, from your code you can connect to it from your code using a standard connection string, but with the addition of creating a ODBC system connection pointing the same SQL Server, and assigning there the custom TCP port.

When you need to connect to SQL Server Management Studio, in the login form you need to use SERVER,PORT.

Enabling dtsx Logging

In SQL Server 2005, to enable dtsx Logging:

  1. open your dtsx in SSMS, then right click and select "Logging" and a dialog box will open
  2. check the root of the tree on the left pane of the dialog box
  3. add a new SSIS log provider, enable it and create a new connection
  4. in the Details tab, select all events, except "Diagnostics"
  5. press the Advanced button, and clear the Computer, Operator, SourceID and ExecutionID columns

To log the value of the dtsx variables:

  1. insert a Script Task where you iterate the dts variabiles via Dts.Variables.GetEnumerator() and log them with the Dts.Log method
  2. in the previous dialog, configure only this task to log the ScriptTaskLogEntry events

Formatting dtsConfig Files

If you write dtsx in SQL Server 2005, often (if not always) you have to make them configurable, in particular if you need to deploy them in various environments (dev, test, staging and production).

Natively the SSMS produces a single-line xml file, so not very readable, but there is a little trick to format them automatically:

  1. open the dtsConfig file
  2. in the editor, right click and "View Data Grid"
  3. expand the grid and make a small modification of a value (for example, add and delete a space)
  4. save the file

Get Command-Line Output from SQL Server 2005

Sometimes, from SQL Server you need to execute a command line application and retrieve its output in a variable.

When you want to to this, you have to consider:

  1. Enabling the execution of the xp_cmdshell stored procedure
  2. Saving the output of the command (maybe more lines) in a temporary table
  3. Taking the first row a putting it in a variable

Finally, here is a complete example. In this case I take the first user in Active Directory whose distinguished name starts with Damiano:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
CREATE TABLE #TEMP (
    LINE nvarchar(4000)
)
INSERT INTO #TEMP EXECUTE master..xp_cmdshell 'dsquery user -name Damiano*'
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
DECLARE @MYVAR nvarchar(512)
SELECT TOP 1 @MYVAR = LINE FROM #TEMP
DROP TABLE #TEMP