List all Indexes in a Database with optimal names

WITH indexCTE AS
(
SELECT st.object_id AS objectID
, SCHEMA_NAME( st.schema_id) AS SchemaName
, st.name AS tableName
, si.index_id AS indexID
, si.name AS indexName
, si.type_desc AS indexType
, sc.column_id AS columnID
, sc.name + CASE WHEN sic.is_descending_key = 1 THEN ‘ DESC’ ELSE ” END AS columnName
, sic.key_ordinal AS ordinalPosition
, CASE WHEN sic.is_included_column = 0 AND key_ordinal > 0 THEN sc.name ELSE NULL END AS indexKeys
, CASE WHEN sic.is_included_column = 1 THEN sc.name ELSE NULL END AS includedColumns
, sic.partition_ordinal AS partitionOrdinal
, CASE WHEN sic.partition_ordinal > 0 THEN sc.name ELSE NULL END AS partitionColumns
, si.is_primary_key AS isPrimaryKey
, si.is_unique AS isUnique
, si.is_unique_constraint AS isUniqueConstraint
, si.has_filter AS isFilteredIndex
, COALESCE(si.filter_definition, ”) AS filterDefinition
FROM sys.tables AS st
INNER JOIN sys.indexes AS si
ON si.object_id = st.object_id
INNER JOIN sys.index_columns AS sic
ON sic.object_id=si.object_id
AND sic.index_id=si.index_id
INNER JOIN sys.columns AS sc
ON sc.object_id = sic.object_id
and sc.column_id = sic.column_id
),
Final AS
(

SELECT DISTINCT
@@SERVERNAME AS ServerName
, DB_NAME() AS DatabaseName
, SchemaName
, tableName
, indexName
, indexType

, STUFF((
SELECT ‘, ‘ + indexKeys
FROM indexCTE
WHERE objectID = cte.objectID
AND indexID = cte.indexID
AND indexKeys IS NOT NULL
ORDER BY ordinalPosition
FOR XML PATH(”),
TYPE).value(‘.’,’varchar(max)’),1,1,”) AS indexKeys
, COALESCE(STUFF((
SELECT ‘, ‘ + includedColumns
FROM indexCTE
WHERE objectID = cte.objectID
AND indexID = cte.indexID
AND includedColumns IS NOT NULL
ORDER BY columnID
FOR XML PATH(”),
TYPE).value(‘.’,’varchar(max)’),1,1,”), ”) AS includedColumns
, COALESCE(STUFF((
SELECT ‘, ‘ + partitionColumns
FROM indexCTE
WHERE objectID = cte.objectID
AND indexID = cte.indexID
AND partitionColumns IS NOT NULL
ORDER BY partitionOrdinal
FOR XML PATH(”),
TYPE).value(‘.’,’varchar(max)’),1,1,”), ”) AS partitionKeys
, isPrimaryKey
, isUnique
, isUniqueConstraint
, isFilteredIndex
, filterDefinition
FROM indexCTE AS cte
–WHERE tableName = ‘SalesOrderDetail’
–WHERE indexName NOT LIKE ‘mtpk_%’ AND indexName NOT LIKE ‘mt_%’
–ORDER BY tableName, indexName
),
AlmostFinal AS
(
— Naming logic
— All Primary keys are UNIQUE
— All Keys may be clustered or NOT
— Indexe may be clustered or NOT
— Indexes may be unique or not

— Add check for LEN > 128
— Add check for indexName GoodName – then EXECUTE sp_rename ‘old key’ ‘new key’

— Need same logic for FKs – when needs to occur first
SELECT *,
CASE WHEN isPrimaryKey = 1 THEN ‘PK’ ELSE
CASE WHEN IsUnique = 1 THEN ‘UX’ ELSE ‘IX’ END
END +
CASE WHEN indexType = ‘CLUSTERED’ THEN ‘C’ ELSE ‘N’ END + ‘_’ +
TableName + ‘_’ + REPLACE( LTRIM( RTRIM( indexKeys ) ), ‘, ‘, ‘_’ )
+ CASE WHEN includedColumns ” THEN ‘#’ + REPLACE( LTRIM( RTRIM( includedColumns ) ), ‘, ‘, ‘_’ ) ELSE ” END AS GoodName
FROM Final
)
SELECT *,
CASE WHEN IndexName GoodName THEN ‘EXECUTE sp_rename N”’ + schemaName + ‘.’ + tableName + ‘.’ + indexName + ”’, N”’ + GoodName + ”” END AS SQLToFix
FROM AlmostFinal

Advertisements

Search Databases and SQL Agent jobs for a String

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

-- =============================================
-- Name : SP_SearchString
-- Author : Doug Osborne
-- Create Date : 06/29/2012
-- Description : This will search a release, or all DBs, for a string
--
-- Note: If searching for 'Status = 'Drop'
-- Code it as 'Status = ''Drop'
--
-- Example : EXECUTE dbo.SP_SearchString @Search='Status = ''Drop', @Environment='', @SpecificBranch='0172', @Type=''--, @Debug=1
-- EXECUTE dbo.SP_SearchString @Search='InboundDataStatus', @Environment='', @SpecificBranch='', @Type=''
/*
DECLARE
@Search NVARCHAR(50);
SET @Search = 'SELECT' + CHAR(9) + '*';
EXECUTE dbo.SP_SearchString @Search=@Search, @Environment='', @SpecificBranch='0177', @Type='V';
*/
-- @Type = 'FN' = 'SQL_SCALAR_FUNCTION'
-- @Type = 'IF' = 'SQL_INLINE_TABLE_VALUED_FUNCTION'
-- @Type = 'C' = 'CHECK_CONSTRAINT'
-- @Type = 'D' = 'DEFAULT_CONSTRAINT'
-- @Type = 'V' = 'VIEW'
-- @Type = 'P' = 'SQL_STORED_PROCEDURE'
-- @Type = 'TF' = 'SQL_TABLE_VALUED_FUNCTION'
-- @Type = 'TR' = 'SQL_TRIGGER'
-- Called By :
-- Revision History - JIRA - Release:
-- Ozzie Created
-- Ozzie Added SSIS packages 07/01/2013
-- =============================================
-- Add code to display how long the search takes in MS - ozzie
/*
DECLARE
@PreviewTextSize INT = 100,
@SearchText VARCHAR(4000) = 'WLResp';

SELECT [Name] AS [SSIS Name]
, REPLACE(REPLACE(SUBSTRING(CAST(CAST(PackageData AS VARBINARY(Max)) AS VARCHAR(Max)), CHARINDEX(@SearchText, CAST(CAST(PackageData AS VARBINARY(MAX)) AS VARCHAR(MAX))) -
@PreviewTextSize / 2, @PreviewTextSize), char(13) + char(10), ''), @SearchText, ' *|' + @SearchText + '|* ') AS [SSIS XML]
FROM MSDB.dbo.sysSSISPackages
WHERE CAST(CAST(PackageData AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE '%' + @SearchText + '%';

--The first step is to setup SSMS to increase the XML output size from the default 2 MB to unlimited.
--This change will allow you to see complete contents of the XML data that represents the SSIS package within MSDB.
-- Within SSMS on the menu tool bar select " Tools --> Options --> Query Results --> Results to Grid ". See the image below for the setting change for before and after.
SELECT [name] AS SSISPackageName
, CONVERT(XML, CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML
FROM MSDB.dbo.sysSSISPackages
WHERE CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), packagedata)) LIKE '%welcomeletter%'
*/
-- =============================================
-- Add logic to SP_SearchColumn here DEO 09/27
ALTER PROCEDURE [dbo].[SP_SearchString]
(
--DECLARE
@Search NVARCHAR(50)= 'AccountID',
@Environment VARCHAR(2) = NULL,
@SpecificBranch VARCHAR(4) = NULL,
@Type VARCHAR(2) = '',
@Debug BIT = 0,
@SearchJobs BIT = 1
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@Current INT = 1,
@Prompt VARCHAR(MAX),
@Total INT,
@Name SYSNAME,
@SQL NVARCHAR(4000),
@Search2 SYSNAME,
@True BIT = 1,
@False BIT = 0,
@PrintMissing BIT,
@CRLF CHAR(2) = CHAR(13) + CHAR(10),
@Local BIT,
@TableOrView VARCHAR(255),
@SingleDB SYSNAME,
@StartTime DATETIME = CURRENT_TIMESTAMP;
-- Add code to replace tabs with a space?
-- Add code to only look at a certain set of release db #'s
DECLARE
@DBS TABLE
(
DBSID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Name SYSNAME NOT NULL
);

IF @SpecificBranch IS NULL
BEGIN
SET @SpecificBranch = ( SELECT MAX( RIGHT( Name, 4 ) ) AS Version FROM sys.databases WHERE Name LIKE 'Enrollment%' AND LEN( Name ) > 10 );
SET @SpecificBranch = ( CASE WHEN ISNUMERIC( @SpecificBranch ) = @True THEN @SpecificBranch ELSE '' END );
END;

IF @Environment IS NULL
BEGIN
SET @Environment = ( SELECT MAX( LEFT( RIGHT( Name, 6 ), 2 ) ) FROM sys.databases WHERE Name LIKE 'Enrollment%' AND LEN( Name ) > 10 );
SET @Environment = ( CASE WHEN @Environment IN ( 'QA', 'PT' ) THEN @Environment ELSE '' END );
END;

IF( @SearchJobs = @True )
BEGIN
SET @Prompt = 'Only looking at code from "' + @Environment + @SpecificBranch + '" branch.';
RAISERROR( '%s', 0, 0, @Prompt ) WITH NOWAIT;
END;

SET @TableOrView =
CASE
WHEN @Type = 'FN' THEN ' AND SO.[Type_Desc] = ''SQL_SCALAR_FUNCTION'''
WHEN @Type = 'IF' THEN ' AND SO.[Type_Desc] = ''SQL_INLINE_TABLE_VALUED_FUNCTION'''
WHEN @Type = 'C' THEN ' AND SO.[Type_Desc] = ''CHECK_CONSTRAINT'''
WHEN @Type = 'D' THEN ' AND SO.[Type_Desc] = ''DEFAULT_CONSTRAINT'''
WHEN @Type = 'V' THEN ' AND SO.[Type_Desc] = ''VIEW'''
WHEN @Type = 'P' THEN ' AND SO.[Type_Desc] = ''SQL_STORED_PROCEDURE'''
WHEN @Type = 'TF' THEN ' AND SO.[Type_Desc] = ''SQL_TABLE_VALUED_FUNCTION'''
WHEN @Type = 'TR' THEN ' AND SO.[Type_Desc] = ''SQL_TRIGGER'''
ELSE ''
END;

-- Set Search Term Here. If you have a wildcard in the search, you must escape it here - eg '_rating' becomes '\_rating'
SELECT
@Search2 = REPLACE( REPLACE(@Search, '_', '\_' ), '%', '\%' ),
@SingleDB = NULL, -- Set just a single database here - SET to NULL to use the LOCAL or ALL Databases option
@PrintMissing = @False,
@Local = @True;

IF CHARINDEX('''', @Search2, 1) > 0
BEGIN
SET @Search2 = REPLACE( @Search2, '''', '''''' );
SET @Search = @Search2;
END;

IF( @SingleDB IS NOT NULL)
BEGIN
INSERT INTO @DBS( Name )
SELECT @SingleDB;
END;
ELSE
BEGIN
IF( @Local = @True AND @SingleDB IS NULL )
BEGIN
INSERT INTO @DBS( Name )
SELECT 'C' + @Environment + @SpecificBranch
UNION ALL
SELECT 'D' + @Environment + @SpecificBranch
UNION ALL
SELECT 'E' + @Environment + @SpecificBranch
UNION ALL
SELECT 'P' + @Environment + @SpecificBranch
UNION ALL
SELECT 'N';
END;
ELSE
BEGIN
INSERT INTO @DBS( Name )
SELECT Name
FROM sys.databases
WHERE Name LIKE '%' + @SpecificBranch
ORDER BY Name ASC;
END;
END;

SELECT @Total = @@ROWCOUNT;

IF( @SearchJobs = @True )
BEGIN
SET @Prompt = CAST( @Total AS VARCHAR(4) ) + ' databases to search for [' + @Search + ']' + @CRLF + @CRLF;
RAISERROR( '%s', 0, 0, @Prompt ) WITH NOWAIT;
END;

WHILE( @Current '' + SO.[Type_Desc] COLLATE Latin1_General_CI_AS + N'' '' + SO.[Name] COLLATE Latin1_General_CI_AS +
'' {'' + REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( SUBSTRING( SC.Text, CHARINDEX ( ''' + @Search + ''', SC.Text ) - 50, 100 ), ''' + @Search + ''', '' '' ), CHAR(13), '' '' ), CHAR(10), '''' ), CHAR(9), '' ''), '' '', '' '' ), '' '', '' '' ), '' '', '' '' ), '' '', '' '' ) + '' }''
FROM [' + @Name + '].sys.syscomments AS SC
INNER JOIN [' + @Name + '].sys.objects AS SO ON SC.ID = SO.OBJECT_ID
WHERE SO.Is_MS_Shipped = 0 ' + @TableOrView + ' AND SC.Text LIKE N''%' + @Search2 + '%'' ESCAPE ''\''
ORDER BY SO.[Type_Desc] ASC, SO.[Name] ASC;
SELECT @T=@@ROWCOUNT, @C=1;
WHILE( @C 1 THEN ''(es)'' ELSE '''' END + ''.'';
SET @Distinct = ( SELECT COUNT( DISTINCT StoredProcedure ) FROM @R WHERE StoredProcedure IS NOT NULL );
PRINT ''Found '' + CAST( @Distinct AS VARCHAR(4)) + '' distinct.''
END
ELSE
BEGIN
IF ' + CAST( @PrintMissing AS VARCHAR(1)) + ' = ' + CAST( @True AS VARCHAR(1)) + '
BEGIN
PRINT ''->Search String Not Located.'' + CHAR(13) + CHAR(10);
END
END';
IF( @Debug = @True )
BEGIN
RAISERROR( '%s', 0, 0, @SQL ) WITH NOWAIT;
END;

EXECUTE ( @SQL );
END;
END TRY
BEGIN CATCH
DECLARE
@Error NVARCHAR(4000);
SET
@Error = ERROR_MESSAGE();
PRINT '->' + @Error + @CRLF + @CRLF;
--EXECUTE dbo.LogSQLError
END CATCH;

SET @Current += 1;
END;

-- Now Look at Jobs
IF( @SearchJobs = @True )
BEGIN
SET @Search2 = N'%' + @Search2 + N'%';

IF EXISTS( SELECT 1
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.sysjobsteps js ON js.job_id = j.job_id
INNER JOIN Master.dbo.sysservers s ON s.srvid = j.originating_server_id
WHERE js.command LIKE @Search2 )
BEGIN
DECLARE @R TABLE( RID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, Result NVARCHAR(4000) NOT NULL );
DECLARE @C INT, @T INT, @A NVARCHAR(512);
INSERT INTO @R( Result )
SELECT 'SQL Jobs';

INSERT INTO @R( Result )
SELECT '->JOB ' + j.name + ' | Step ' + CAST( js.step_id AS VARCHAR(3) ) + ' {' + js.command + '}'
FROM MSDB.dbo.sysjobs j
INNER JOIN MSDB.dbo.sysjobsteps js ON js.job_id = j.job_id
INNER JOIN Master.dbo.sysservers s ON s.srvid = j.originating_server_id
WHERE js.command LIKE '%WelcomeLetterImport%';

SELECT @T=@@ROWCOUNT, @C=1;
WHILE( @C 1 THEN '(es)' ELSE '' END + '.';
END;
END;

IF( @SearchJobs = @True )
BEGIN
PRINT @CRLF + 'Search Time ' + CAST( DATEDIFF( ms, @StartTime, CURRENT_TIMESTAMP ) AS VARCHAR ) + ' milliseconds.';
END;
END;

Shrink all of the Log Files on a database

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[SP_ShrinkAllLogs]’) AND TYPE IN (N’P’,N’PC’))
BEGIN
 DROP PROCEDURE [dbo].[SP_ShrinkAllLogs]
END
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
— =============================================
— Name   : SP_ShrinkAllLogs
— Author  : Doug Osborne
— Create Date : 06/28/2012
— Description : This will shrink all logs on the DB
— Example  : EXECUTE dbo.SP_ShrinkAllLogs
— Called By :
— Revision History – JIRA – Release:
— =============================================
CREATE PROCEDURE [dbo].[SP_ShrinkAllLogs]
AS
BEGIN
 SET NOCOUNT ON;

 EXECUTE dbo.sp_msforeachdb

USE ?

DECLARE
 @Log SMALLINT,
 @DB  SYSNAME;

SELECT @Log = FILE_ID,
@DB = Name
FROM sys.database_files
WHERE type_desc = ”Log”;

DBCC SHRINKFILE( @Log, TRUNCATEONLY );

RAISERROR(”[%s]”, 0, 0, @DB ) WITH NOWAIT;

END

Validate all views in all databases on a server

— Douglas Osborne 05/09/2011 SQLOzzie@WordPress.com
EXECUTE dbo.sp_msforeachdb

USE ?

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE
@Count INT,
@Active INT,
@View SYSNAME,
@SQL VARCHAR(1024),
@ErrorMessage NVARCHAR(2048), — Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
@CRLF CHAR(2), — Carriage Return + Line Feed
@Msg NVARCHAR(2000), — Standard SQL error message to log
@DN VARCHAR(128)

SET @DN = DB_NAME()

IF @DN NOT IN ( ”master”, ”model”, ”msdb”, ”tempdb” )
BEGIN
PRINT CHAR(45) + CHAR(62) + @DN;
CREATE TABLE #AllViews
(
AllViewsID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
AllViewName SYSNAME NOT NULL
)
CREATE TABLE #AllErrors
(
AllErrorsID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
AllErrorName NVARCHAR(1024) NOT NULL
)

INSERT INTO [#AllViews] ( [AllViewName] )
SELECT [Name]
FROM sys.[views]
ORDER BY [Name] ASC

SELECT @Count = @@ROWCOUNT, @Active = 1

WHILE( @Active <= @Count )
BEGIN
SELECT @View = [AllViewName]
FROM [#AllViews]
WHERE [AllViewsID] = @Active

SET @SQL = ''DECLARE @A INT IF EXISTS( SELECT TOP 1 * FROM dbo.'' + @View + '') BEGIN SET @A = 1 END''
BEGIN TRY
EXECUTE ( @SQL )
END TRY
BEGIN CATCH
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@CRLF = CHAR(13) + CHAR(10)

SET @Msg = ''Database ['' + @DN + ''] View ['' + @View + '']-['' + @ErrorMessage + '']''

INSERT INTO [#AllErrors] ( [AllErrorName] )
SELECT @Msg

RAISERROR( ''%s'', 0, 0, @Msg ) WITH NOWAIT;
END CATCH

SET @Active = @Active + 1
END
END'

Foreign Key Constraints marked as not trusted – let’s fix that

One of the rather well hidden aspects of foreign keys is the fact that even though they are there – they might not be acknowledged by the system as being ‘trusted’. You can quickly determine if that has occurred on your system by running the following query:

SELECT Name AS [Constraint], object_name(parent_object_id) AS [Table]
FROM sys.foreign_keys
WHERE Is_Not_Trusted = 1

Here is a quick routine to fix this on your database. If you incorporate this into one of my earlier scripts, you can easily run it on all of your databases in your instance.

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE
    @BadConstraints        INT,
    @CurrentConstraint    INT,
    @FixTrustSQL        VARCHAR(1024)

CREATE TABLE #FixTrust
(
    FixTrustID    INT IDENTITY(1,1)    NOT NULL PRIMARY KEY CLUSTERED,
    FixTrustSQL    VARCHAR(1024)        NOT NULL
)

INSERT INTO #FixTrust( FixTrustSQL )
SELECT ‘ALTER TABLE [dbo].[‘ + OBJECT_NAME( Parent_Object_ID ) + ‘] WITH CHECK CHECK CONSTRAINT ‘ + Name AS [SQL to Execute]
FROM sys.foreign_keys
WHERE Is_Not_Trusted = 1
ORDER BY OBJECT_NAME( Parent_Object_ID ) ASC

SELECT @BadConstraints = @@ROWCOUNT, @CurrentConstraint = 1

WHILE( @CurrentConstraint <= @BadConstraints )
BEGIN
    SELECT @FixTrustSQL = FixTrustSQL
    FROM #FixTrust
    WHERE FixTrustID = @CurrentConstraint

    BEGIN TRY
        RAISERROR( ‘%s’, 0, 0, @FixTrustSQL ) WITH NOWAIT;
        EXECUTE ( @FixTrustSQL )
    END TRY
    BEGIN CATCH
            DECLARE
            @ErrorNumber    INT,            — Returns the number of the error
            @ErrorSeverity    INT,            — Returns the severity
            @ErrorState        INT,            — Returns the error state number
            @ErrorProcedure    SYSNAME,        — Returns the name of the stored procedure or trigger where the error occurred
            @ErrorLine        INT,            — Returns the line number inside the routine that caused the error
            @ErrorMessage   NVARCHAR(2048),    — Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times
            @CRLF            CHAR(2),        — Carriage Return + Line Feed
            @Msg            VARCHAR(2000)    — Standard SQL error message to log

        SELECT
            @ErrorNumber        = ERROR_NUMBER(),
            @ErrorSeverity        = ERROR_SEVERITY(),
            @ErrorState            = ERROR_STATE(),
            @ErrorProcedure        = ISNULL( ERROR_PROCEDURE(), ‘inline code’ ),
            @ErrorLine            = ERROR_LINE(),
            @ErrorMessage        = ERROR_MESSAGE(),
            @CRLF                = CHAR(13) + CHAR(10)

        SET @Msg = @CRLF +
        ‘Error [‘ + CAST( @ErrorNumber AS VARCHAR ) + ‘] Severity [‘ + CAST( @ErrorSeverity AS VARCHAR ) + ‘] State [‘ + CAST( @ErrorState AS VARCHAR ) + ‘].’ + @CRLF +
        ‘Procedure [‘ + @ErrorProcedure + ‘] Line [‘ + CAST( @ErrorLine AS VARCHAR ) + ‘].’ + @CRLF +
        ‘Message [‘ + @ErrorMessage + ‘]’

        RAISERROR( ‘%s’, 0, 0, @Msg ) WITH NOWAIT;
    END CATCH

    SET @CurrentConstraint = @CurrentConstraint + 1
END

IF OBJECT_ID(‘[tempdb].[dbo].#FixTrust’, ‘U’) IS NOT NULL
BEGIN
    DROP TABLE [tempdb].#FixTrust
END