CREATE FUNCTION [dbo].[fn_StripCharacters]
(   @String          NVARCHAR(MAX),
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression = '%['+@MatchExpression+']%'
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = STUFF(@String, PatIndex(@MatchExpression,@String), 1, '')
    RETURN @String
END

SELECT dbo.fn_StripCharacters('a1$%s#22d&39#f4$', '^a-z') as OnlyAlphaChars

The sp_helpindex stored procedure gives useful information like name, description and key about the indexes on a table or view. However for retrieving detailed information, you should make use of the sys.indexes object catalog view.

Let us say you want to view the Index MetaData for the table Orders in the Northwind database.

Here’s the query for it:

SELECT
    name,
    type_desc,
    is_unique,
    allow_row_locks,
    allow_page_locks,
    is_disabled,
    fill_factor
    FROM sys.indexes
    WHERE object_id = OBJECT_ID('Orders')

OUTPUT

sp_MSforeachdb iterates through each database in a SQL Server instance. Instead of using a cursor, this Undocumented Stored Procedure is of immense help when I need to run a command against all the databases in my local server. Here are some scenarios where the sp_MSforeachdb can be practically used for your day to day tasks:

1. Print all the database names in a SQL Server Instance



EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'



2. Print all the tables in all the databases of a SQL Server Instance



EXEC sp_MSforeachdb 'USE ? SELECT DB_NAME() + ''.'' + OBJECT_NAME(object_Id) FROM sys.tables'



3. The example shown above prints the tables for master, model, msdb and tempdb. If you want to eliminate these databases in the query use, do the following:



EXEC sp_MSforeachdb '
    USE ?
    SELECT OBJECT_NAME(object_Id)
        FROM sys.tables
            where DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
'



4. Display the size of all databases in a SQL Server instance



EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'



5. Determine all the physical names and attributes(size,growth,usage) of all databases in a SQL Server instance



EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'



6. Change Owner of all databases to 'sa'



EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''



7. Check the Logical and Physical integrity of all objects in the database



sp_MSforeachdb 'DBCC CHECKDB(?)' 


Similarly you can backup all databases at one go or do a CHECKSUM using this useful procedure. There are many more ways to use it. Got a useful tip on sp_MSforeachdb? Share it using the comments below.

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure:

1. Display the size of all tables in a database



USE NORTHWIND
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"



2. Display Number of Rows in all Tables in a database



USE YOURDBNAME
EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'



3. Rebuild all indexes of all tables in a database



USE YOURDBNAME
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"



Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."

4. Disable all constraints of all tables in a database



USE YOURDBNAME
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL" 



5. Disable all Triggers of all tables in a database



USE YOURDBNAME
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'



6. Delete all data from all tables in your database



-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
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



7. To RESEED all table to 0, use this script



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


The two tips shown above have been taken from http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx and http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx


8. Reclaim space from dropped variable-length columns in tables or indexed views



USE YOURDBNAME
EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';



9. Update Statistics of all Tables in a database



USE YOURDBNAME
EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

Welcome to SQL Server Examples

The examples on this portal will provide you with the building blocks necessary to quickly resolve different TSQL coding issues. You can browse the examples by using the Examples link on the main menu. You can also download all the examples on this site by clicking HERE.

Also available on this site is a SQL Server monitoring tool known as the SQL Server DBA Dashboard tool. This tool provides both high level and detailed reports to help monitor your SQL Server instances. With this tool you can quickly identify the resources used by specific TSQL statements and stored procedures.

• Working with XML in SQL Server
  
• Powershell for SQL Server
  
• Webcasts and Presentations on SQL Server
  
• Resolving connection issues to SQL Server