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
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
DeleteStatement = CASE
WHEN fk.object_id IS NULL
THEN ‘TRUNCATE TABLE ‘
+ t.[name] + ‘ — ‘ + ISNULL(CONVERT(VARCHAR,i.[rows]),‘UNKNOWN’) + ‘ rows’
+ 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
Software Dawg 