How to script all indexes from a table (or an entire database)

SELECT ' CREATE ' +  
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX [' +    
    I.name  + '] ON '  +   
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  
    KeyColumns + ' )  ' +  
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  
    -- default value  
    'SORT_IN_TEMPDB = OFF '  + ','  +  
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  
    -- default value   
    --' DROP_EXISTING = ON '  + ','  +  
    -- default value   
    ' ONLINE = OFF '  + ','  +  
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  
   DS.name + ' ] '  [CreateIndexScript]  
FROM sys.indexes I    
 JOIN sys.tables T ON T.Object_id = I.Object_id     
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
 JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 0   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id    
    GROUP BY IC1.object_id,C.name,index_id   
    ORDER BY MAX(IC1.key_ordinal)   
       FOR XML PATH('')), 1, 2, '') KeyColumns    
    FROM sys.index_columns IC2    
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
 LEFT JOIN (SELECT * FROM (    
    SELECT IC2.object_id , IC2.index_id ,    
        STUFF((SELECT ' , [' + C.name  + ']'
    FROM sys.index_columns IC1    
    JOIN Sys.columns C     
       ON C.object_id = IC1.object_id     
       AND C.column_id = IC1.column_id     
       AND IC1.is_included_column = 1    
    WHERE IC1.object_id = IC2.object_id     
       AND IC1.index_id = IC2.index_id     
    GROUP BY IC1.object_id,C.name,index_id    
       FOR XML PATH('')), 1, 2, '') IncludedColumns     
   FROM sys.index_columns IC2     
   WHERE IC2.Object_id = object_id('tblERPInvoiceData') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2     
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
AND I.Object_id = object_id('tblERPInvoiceData') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Continue reading “How to script all indexes from a table (or an entire database)”

How to get only alpha or alphanumeric characters from a string in SQL

How to get only alpha chars in SQL

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

Capture

How to get alphanumeric characters in SQL

Create Function [dbo].[RemoveNonAlphaNumCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z0-9]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaNumCharacters('abc1234def5678ghi90jkl')

Capture

Select dbo.RemoveNonAlphaNumCharacters('ab£2+2(")£c123£%"&--4def567&£%"(8ghi90jkl')

Capture

SQL Function to identify invalid email addresses in the database

When you are manipulating large quantities of data, you might have some records containing email addresses which slip through as invalid.

Following function checks these conditions:

No embedded spaces
‘@’ can’t be the first character of an email address
‘.’ can’t be the last character of an email address
There must be a ‘.’ somewhere after ‘@’
the ‘@’ sign is allowed
Domain name should end with at least 2 character extension
can’t have patterns like ‘.@’ and ‘..’

--print dbo.fnc_IsValidEmail('aaa@aaa.com')
--print dbo.fnc_isValidEmail('Mi email@test.com')
CREATE FUNCTION fnc_IsValidEmail
(@Email_Address varchar(255))
RETURNS BIT
BEGIN
DECLARE @Valid bit
SET @Valid = 0
if @Email_Address=''
SET @Valid = 1
ELSE

IF (
CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0
AND LEFT(LTRIM(@email_address),1) <> '@'
AND RIGHT(RTRIM(@email_address),1) <> '.'
AND CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1
AND LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1
AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3
AND (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
SET @Valid = 1

RETURN @Valid
END

How to call:

SELECT Email, dbo.fnc_IsValidEmail(Email) as Valid
from tblSuppliers
WHERE Email”

Capture

How to trim leading zeroes in a column/string in SQL

If you have been using this:

SUBSTRING(str_col, PATINDEX('%[^0]%', str_col), LEN(str_col))

to get your zeroes removed from a string, you might be interested in a variation that only removes the leading zeroes. The T-SQL Function has been created to be called from either Stored Procedure code or Triggers.

--print dbo.ufn_TrimLeadingZeros ('0100 ATN652')
ALTER FUNCTION [dbo].[ufn_TrimLeadingZeros] ( @Input VARCHAR(50) )
RETURNS VARCHAR(50)
AS
BEGIN
    --RETURN REPLACE(LTRIM(REPLACE(@Input, '0', ' ')), ' ', '0')
    RETURN SUBSTRING(@Input, PATINDEX('%[^0]%', @Input+'.'), LEN(@Input))
END

How to restore all SQL Server database backups from a folder

Sometimes, as a DBA, you will need to restore in bulk hundreds of databases from a server backup to another server.
To do this easily, we created this script that will back up all databases.

This is the “Restore” script at the other end.


CREATE PROCEDURE Restorealldbs

AS

SET nocount ON
— 1 – Variable declaration 
DECLARE @dbName SYSNAME
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE
(
backupfile NVARCHAR(255)
)

DECLARE @lastFullBackup NVARCHAR(500)

DECLARE @lastDiffBackup NVARCHAR(500)

DECLARE @backupFile NVARCHAR(500)

DECLARE @FileListTbl TABLE

(
logicalname          NVARCHAR(128) NOT NULL,
physicalname         NVARCHAR(260) NOT NULL,
type                 CHAR(1) NOT NULL,
filegroupname        NVARCHAR(120) NULL,
size                 NUMERIC(20, 0) NOT NULL,
maxsize              NUMERIC(20, 0) NOT NULL,
fileid               BIGINT NULL,
createlsn            NUMERIC(25, 0) NULL,
droplsn              NUMERIC(25, 0) NULL,
uniqueid             UNIQUEIDENTIFIER NULL,
readonlylsn          NUMERIC(25, 0) NULL,
readwritelsn         NUMERIC(25, 0) NULL,
backupsizeinbytes    BIGINT NULL,
sourceblocksize      INT NULL,
filegroupid          INT NULL,
loggroupguid         UNIQUEIDENTIFIER NULL,
differentialbaselsn  NUMERIC(25, 0) NULL,
differentialbaseguid UNIQUEIDENTIFIER NULL,
isreadonly           BIT NULL,
ispresent            BIT NULL,
tdethumbprint        VARBINARY(32) NULL
);

— 2 – Initialize variables 

SET @dbName = 

SET @backupPath = ‘D:SQLBackup20150306’

— 3 – get list of files 

SET @cmd = ‘DIR /b ‘ + @backupPath

INSERT INTO @fileList

(backupfile)

EXEC master.sys.Xp_cmdshell

@cmd

DECLARE @SQL NVARCHAR(1500)

DECLARE @BiGSQL NVARCHAR(max)

SET @BiGSQL=

DECLARE db_cursor CURSOR FOR
SELECT backupfile
FROM   @fileList
OPEN db_cursor

FETCH next FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @file NVARCHAR(255)

DECLARE @RestoreStatement NVARCHAR(max)

SET @BackupFile = @backupPath + @dbName

SET @RestoreStatement = N’RESTORE FILELISTONLY  FROM DISK=N”’ + @BackupFile + ””

DELETE FROM @FileListTbl
INSERT INTO @FileListTbl
EXEC(@RestoreStatement);

DECLARE @logical_data NVARCHAR(max),

@logical_log  NVARCHAR(max);

SET @logical_data = (SELECT logicalname FROM   @FileListTbl WHERE  type = ‘D’ AND fileid = 1)

SET @logical_log = (SELECT logicalname FROM   @FileListTbl WHERE  type = ‘L’ AND fileid = 2)

SET @dbName = Replace(@DbName, ‘.BAK’, )

SET @sql = N’RESTORE database ‘ + @dbName + ‘ from disk =N”’ + @BackupFile + ”’ with ‘;

SET @sql = @sql + N’ move ”’ + @logical_data + N”’ to ”D:SQL Databases’ + @logical_data + ‘.mdf” ,’ 

SET @sql = @sql + N’ move ”’ + @logical_log + N”’ to ”D:SQL Databases’ + @logical_log + ‘.ldf”  ‘

IF Isnull(@SQL, ) <> 

SET @BiGSQL = @BiGSQL + 
 + @SQL

FETCH next FROM db_cursor INTO @dbName

END

CLOSE db_cursor
DEALLOCATE db_cursor
PRINT @BiGSQL
EXEC Sp_executesql @BiGSQL

Looking for the SagePayTransaction table for nopCommerce?

0002619

Look no further.

CREATE TABLE [dbo].[SagePayServerTransaction](
[Id] [int] IDENTITY(1,1) NOT NULL,
[VPSTxId] [nvarchar](max) NULL,
[SecurityKey] [nvarchar](max) NULL,
[NotificationResponse] [nvarchar](max) NULL,
[VendorTxCode] [nvarchar](max) NULL,
[VPSSignature] [nvarchar](max) NULL,
[Status] [nvarchar](max) NULL,
[StatusDetail] [nvarchar](max) NULL,
[TxAuthNo] [nvarchar](max) NULL,
[AVSCV2] [nvarchar](max) NULL,
[AddressResult] [nvarchar](max) NULL,
[PostCodeResult] [nvarchar](max) NULL,
[CV2Result] [nvarchar](max) NULL,
[GiftAid] [nvarchar](max) NULL,
[ThreeDSecureStatus] [nvarchar](max) NULL,
[CAVV] [nvarchar](max) NULL,
[AddressStatus] [nvarchar](max) NULL,
[PayerStatus] [nvarchar](max) NULL,
[CardType] [nvarchar](max) NULL,
[Last4Digits] [nvarchar](max) NULL,
[CreatedOnUtc] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO