List all indexes in a database

This script will list all indexes and their types (clustered/nonclustered).

SELECT  so.name AS TableName
            , si.name AS IndexName
            , si.type_desc AS IndexType
FROM            sys.indexes si 
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE so.type = 'U'    --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY so.name, si.type 
[tweetmeme only_single=”false”]
Advertisements

List all stored procedures in a database

This script will provide a list of all stored procedures in a database along with the create script for each.

SELECT specific_name, routine_definition 
  FROM information_schema.routines 
 WHERE routine_type = 'PROCEDURE'
 
[tweetmeme only_single=”false”]

List all synonyms in a database

This script will list all synonyms you have in a database.  It will tell you the name, the server connecting to, the database, schema, and object name your connecting to.

This is helpful to figure out if you have your synonyms pointing to the wrong environment.

SELECT name, 
COALESCE(PARSENAME(base_object_name,4),@@servername) AS serverName, 
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName, 
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName, 
PARSENAME(base_object_name,1) AS objectName 
FROM sys.synonyms 
ORDER BY serverName,dbName,schemaName,objectName
[tweetmeme only_single=”false”]

Undocumented stored procedure for looping through all tables in a database

I got tired of writing code to loop through all tables in a database.  I found out about an undocumented stored procedure that does most of that for me.
The stored procedure name is sp_MSforeachtable it takes one input parameter (@command).
This simplifies so much code (no more looping through sysobjects).

Here are some examples of how to use it.

This command will return a row count for each table in your database.

EXEC sp_MSforeachtable ‘SELECT ‘‘?’‘, Count(*) as NumberOfRows FROM ?’

Notice the question mark (?), that gets substituted as the table (in the sp).
So basically any command you can do against a table you can use and put a ? where you put a table name.

Here is how to rebuild all indexes.

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

Here is how you update all statistics on your tables.

EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

You can also add additional conditional logic if you need to restrict your commands to a subset of tables.
This is very powerful. You don’t always need to hit all tables with a command.

This one will loop through all your tables and add the Column CreatedOn on any table that doesn’t already have it.

EXEC sp_MSforeachtable '
    if not exists (select column_name from INFORMATION_SCHEMA.columns 
                   where table_name = ''?'' and column_name = ''CreatedOn'') 
    begin
        ALTER TABLE [?] ADD CreatedOn datetime NOT NULL DEFAULT getdate();
    end
'
[tweetmeme only_single=”false”]

Tulsa SQL Server User Group presentation

I presented at the Tulsa SQL Server user group on 4-9-2013.  I spoke about support scripts for the app developers sql server toolbox.
For those that came, thanks for showing up and listening to me.

Attached is my presentation.
SQL Server Support Scripts

You will have to rename it from .pptx to .zip.
Wordpress won’t let up upload a zip file.
I wanted to include the sql scripts with the powerpoint presentation.

If you just want the powerpoint here is the link for that.
SQL Server Support Scripts

I’d love to hear any thoughts or feedback about it.

How to view SQL Server property information

Run the query below to find out the property information about SQL Server.
This is helpful when try to determine which features (based on the version) that are available.

 SELECT  'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
 UNION ALL  SELECT 'Collation', SERVERPROPERTY('Collation')
 UNION ALL  SELECT 'CollationID', SERVERPROPERTY('CollationID')
 UNION ALL  SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
 UNION ALL  SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
 UNION ALL  SELECT 'Edition', SERVERPROPERTY('Edition')
 UNION ALL  SELECT 'EditionID', SERVERPROPERTY('EditionID')
 UNION ALL  SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
 UNION ALL  SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
 UNION ALL  SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
 UNION ALL  SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
 UNION ALL  SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
 UNION ALL  SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
 UNION ALL  SELECT 'LCID', SERVERPROPERTY('LCID')
 UNION ALL  SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
 UNION ALL  SELECT 'MachineName', SERVERPROPERTY('MachineName')
 UNION ALL  SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
 UNION ALL  SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
 UNION ALL  SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
 UNION ALL  SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
 UNION ALL  SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
 UNION ALL  SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
 UNION ALL  SELECT 'ServerName', SERVERPROPERTY('ServerName')
 UNION ALL  SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
 UNION ALL  SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
 UNION ALL  SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
 UNION ALL  SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

If you want a very simple view of the version info run this query.
Select @@version as Version

How to tell what SQL objects have been added/updated recently.

Sometimes it helps to see what database objects have been added/updated recently.  If you pick up support of a new database it helps to see where the latest action has been going on. Or if you are doing development and want to make sure you have all the objects that need to get moved to the next environment.

Here is code that will return what objects have been created and when they were created *.
Just set the @dateAddedToGoBack  variable to how many days back you need.

DECLARE @dateAddedToGoBack int = 30

SELECT name, modify_date
FROM sys.objects
WHERE type IN ('P', 'V', 'U', 'PK', 'TR') --SQL_STORED_PROCEDURE, VIEW, USER_TABLE, PRIMARY_KEY_CONSTRAINT, SQL_TRIGGER
AND DATEDIFF(D,create_date, GETDATE()) < @dateAddedToGoBack


Here is code that will return what objects have been updated and when they were updated *.
Just set the @dateUpdatedToGoBack variable to how many days back you need.

DECLARE @daysUpdatedToGoBack int = 30

SELECT name, modify_date
FROM sys.objects
WHERE type IN ('P', 'V', 'U', 'PK', 'TR') --SQL_STORED_PROCEDURE, VIEW, USER_TABLE, PRIMARY_KEY_CONSTRAINT, SQL_TRIGGER
AND DATEDIFF(D,modify_date, GETDATE()) < @daysUpdatedToGoBack

* Caveat: If you are doing something like a sp_refreshview or sp_recompile it could skew your results (ie. the modify dates will be the last time run).

Updated – Search for string across all fields in all tables in a database

This is an update to a previous post.

It has been updated to use table variables instead of temp tables.
This helps when you are in an environment (like production) where you don’t have permissions to create/drop temp tables.

DECLARE @Results TABLE (ColumnName nvarchar(370),   ColumnValue nvarchar(3630))

DECLARE @SearchStr nvarchar(100)

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''
SET @SearchStr = 'STRING TO SEARCH FOR'

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = '' SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped' ) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO @Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM @Results

Grant execute permissions to all stored procs easily

Granting execute permissions can be stored procedures can be quite a pain, especially when you have dropped and recreated your stored procedures when moving between environments.

You can go to each stored procedure and right click each one and change the permissions.  That takes forever.

A quick way to resolve the issue (in SQL 2005 and SQL 2008) is to create a role and give it execute permissions at that database level.

Here is the SQL to run:

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

 

This will create a SQL role and anyone you put in it will will have execute permissions to all stored procedures in the database.

This doesn’t work if you only want to grant execute to a subset of stored procedures.

Technorati Tags: