Equinox Software Consultancy Limited

The Useful Tips Page...

Scripts to enable CSV/Excel import and xp_cmdshell usage in SQL Server

/* -----------------------------------------*/
                        /* OPENROWSET - required for CSV imports */
                        exec sp_configure 'show advanced options', 1
                        go
                        reconfigure
                        go
                        exec sp_configure 'Ad Hoc Distributed Queries', 1
                        go
                        reconfigure
                        go

/* -----------------------------------------*/
                        /* enable xp_cmdshell  */
                        exec sp_configure 'show advanced options', 1
                        go
                        reconfigure
                        go
                        exec sp_configure 'xp_cmdshell', 1
                        go
                        reconfigure
                        go

/* -----------------------------------------*/
                        /* xp_cmdshell - granting xp_cmdshell access to a user (domain and/or SQL user)*/
                        use master
                        go
                        grant execute on master..xp_cmdshell to username

/* -----------------------------------------*/
                        /* xp_cmdshell - If using a SQL user, grant access to the xp_cmdshell through a domain user */
                        use master
                        go
                        Exec sp_xp_cmdshell_proxy_account �domain/username�, �password�

Escaping a character in a wild card query

Create a Date formatted to YYYY-MM, zero padding the month

Help in determining whether indexes are required in SQL Server

declare @database_name varchar(255)

set @database_name = '<Database name to check>
                            '

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT index_advantage,'CREATE INDEX IX_' + REPLACE(REPLACE(COALESCE(equality_columns, inequality_columns), '[', ''), ']', '')
                            + ' ON ' + d.statement + '(' + COALESCE(equality_columns, inequality_columns)
                            + CASE WHEN equality_columns IS NOT NULL THEN
                            CASE WHEN inequality_columns IS NOT NULL THEN ', ' + inequality_columns
                            END
                            END
                            + ')' + CASE WHEN included_columns IS NOT NULL THEN ' INCLUDE (' + included_columns + ')' END
                            FROM sys.dm_db_missing_index_details d
                            join sys.dm_db_missing_index_groups as mig on mig.index_handle=d.index_handle
                            and d.database_id = (select database_id from sys.databases where name = @database_name)
                            join (select user_seeks * avg_total_user_cost * (avg_user_impact*0.01) as index_advantage,
                            migs.* from sys.dm_db_missing_index_group_stats migs) as migs_adv
                            on migs_adv.group_handle=
                            mig.index_group_handle
                            order by index_advantage desc

/*based on this formula, anything greater than 5000 indicates a possible,
                            and anything greater than 10000
                            is almost a definite*/

Check for index fragmentation