MS-SQL ANSI PADDING DEMONSTRATION

SET ANSI_PADDING OFF
GO
CREATE TABLE trimtrailer(a varchar(12) NOT NULL)
GO
INSERT trimtrailer(a) VALUES ('AnsiPaddingOff ')
GO
SET ANSI_PADDING ON
GO
ALTER TABLE trimtrailer ALTER COLUMN a varchar(12) NOT NULL
GO
INSERT trimtrailer(a) VALUES ('AnsiPaddingOn ')
GO
SELECT '<' + a + '>' FROM trimtrailer
GO
DROP TABLE trimtrailer
 
Advertisements

Visual Basic – Convert String to Date in the correct way

This method of converting a string to a date works regardless of the Region settings on the client machine.

 

Dim dtTestDate As Date
Dim sTestDate As String
sTestDate = "120508" ' May 8, 2012
dtTestDate = Date.ParseExact(sTestDate.Substring(2, 2) & "/" & sTestDate.Substring(4, 2) & "/" & sTestDate.Substring(0, 2), "MM/dd/yy"New System.Globalization.CultureInfo("EN-US"), System.Globalization.DateTimeStyles.None)

MS-SQL capitalize all table names

DECLARE @TableName NVARCHAR(500)
DECLARE @NewTableName sysname
DECLARE A CURSOR FOR SELECT [name] FROM sys.objects o WHERE o.[type] = ‘U’
OPEN A
FETCH NEXT FROM A INTO @TableName
WHILE(@@FETCH_STATUS=0)
BEGIN
  SET @NewTableName = UPPER(@TableName)
  IF @TableName COLLATE SQL_Latin1_General_CP1_CS_AS <> @NewTableName COLLATE SQL_Latin1_General_CP1_CS_AS
  BEGIN
    EXEC sp_rename @TableName, @NewTableName, ‘object’
  END
  FETCH NEXT FROM A INTO @TableName
END
CLOSE A
DEALLOCATE A

Find which MS-SQL tables have the most rows

 

SELECT
    t.[name] AS TableName,
    ISNULL(CONVERT(VARCHAR,i.[rows]),‘UNKNOWN’) AS TableRowCount
FROM
    sys.tables t
    LEFT JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)
ORDER BY i.rows

 

….and then create a database script for clearing them out:

 

SELECT
DeleteStatement = CASE
        WHEN fk.object_id IS NULL
        THEN ‘TRUNCATE TABLE ‘
+ t.[name] + ‘ — ‘ + ISNULL(CONVERT(VARCHAR,i.[rows]),‘UNKNOWN’) + ‘ rows’
        ELSE ‘DELETE ‘ + t.[name] + ‘ — ‘ + ISNULL(CONVERT(VARCHAR,i.[rows]),‘UNKNOWN’) + ‘ rows’
    END
FROM
    sys.tables t
    LEFT JOIN sysindexes i ON (t.object_id = i.id AND i.indid < 2)
    LEFT JOIN sys.foreign_keys fk ON (fk.parent_object_id = t.object_id)

ORDER BY i.rows DESC

 
 

A query to find MS-SQL missing indexes

 

SELECT
    dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans)
Avg_Estimated_Impact,
dm_migs.last_user_seek AS
Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
‘CREATE INDEX
[IDX_’
+ OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ‘_’
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,),‘, ‘,‘_’),‘[‘,),‘]’,) +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ‘_’
ELSE
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,),‘, ‘,‘_’),‘[‘,),‘]’,)
+ ‘]’
+ ‘ ON ‘ + dm_mid.statement
+ ‘ (‘ + ISNULL (dm_mid.equality_columns,)
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ‘,’ ELSE
END
+ ISNULL (dm_mid.inequality_columns, )
+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + dm_mid.included_columns
+ ‘)’, ) AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats
dm_migs
ON dm_migs.group_handle =
dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details
dm_mid
ON dm_mig.index_handle =
dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO