TODO:
Have you ever wanted to convert the data in a column to INIT CAP? For example you have data 'TEST DATA' and you want to change it to 'Test Data'?
SOLUTION:
CREATE FUNCTION dbo.fCapFirst(@pcIn NVARCHAR(4000))
RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @i INT
, @cWork NVARCHAR(4000)
SET @cWork = Upper(Left(@pcIn, 1)) + Lower(SubString(@pcIn, 2, 4000))
SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
, @cWork COLLATE Latin1_General_BIN)
WHILE 0 < @i
BEGIN
SET @cWork = Left(@cWork, @i)
+ Upper(SubString(@cWork, 1 + @i, 1)) + SubString(@cWork, 2 + @i, 4000)
SET @i = PatIndex('%[^0-9A-Za-z][a-z]%'
, @cWork COLLATE Latin1_General_BIN)
END
RETURN @cWork
END
GO
NOTES:
This solution was posted by user Pat Phelan on this site.
To run issue this command: update MyTable set Mycolumn= dbo.fCapFirst(Mycolumn)
TODO:
Have you ever wanted to check to see if an update or other error occurred in your stored procedure. To do so check the @@ERROR variable, if it is 0, then no errors occurred.
SOLUTION:
IF @@ERROR = 0
BEGIN
--return the id of the row inserted
SELECT @@ROWCOUNT As RowsAffected
END
ELSE
BEGIN
SELECT -1 As RowsAffected
END
NOTES:
In the example above, I did not care about the error code, which is why i chose to return -1 in the ELSE.
TODO:
Have you ever wanted to reseed an Identity column in your database?
SOLUTION:
DBCC CHECKIDENT (yourtablename, reseed, 1)
NOTES:
The above command will reset the identity to start at 1. You can set that to any value you would like.
TODO:
Have you ever wanted to list all tables that were user created using T-SQL?
SOLUTION:
SELECT name FROM SYS.OBJECTS WHERE TYPE='U' order by name
NOTES:
There are no notes on this topic
TODO:
Have you ever wanted to get information on SQL Server Mainenance Plan job steps using T-SQL?
SOLUTION:
select * From msdb..sysjobs
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to find text that exists in a stored procedure, function or trigger using T-SQL?
SOLUTION:
SELECT DISTINCT *
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%enter your search criteria here%'
ORDER BY 2,1
NOTES:
There are no notes on this topic.
TODO:
Have you ever wanted to create a table from the results of a SELECT statement?
SOLUTION:
select * into newtable_tmp from oldtable
NOTES:
This statement will select all columns and records from the table "oldtable", and create a new table called "newtable_tmp" that contains the results of the SELECT statement.
TODO:
Have you ever wanted to get a list of all of the columns in a database table, and their details, ordered by name?
SOLUTION:
SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,ISNULL(CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH),'')
,IS_NULLABLE
,ISNULL(COLUMN_DEFAULT,'')
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'tbl_name_here'
ORDER BY
COLUMN_NAME ASC;
NOTES:
No notes on this topic.
TODO: Have you ever wanted to re-indentity a column in SQL Server?
SOLUTION:
T-SQL
DECLARE @Count INT
SET @Count = 0
UPDATE [dbname].[dbo].[table]
SET @Count = [id] = @Count+1
NOTES:
Identity Insert must be enabled.
"Id" is the name of the identity column.
TODO:
Have you ever wanted to remove duplicate entries from a table using T-SQL?
SOLUTION:
BEGIN TRANSACTION
Delete from MyTable where row_id <
(Select Max(row_id) from MyTable mt where MyTable.Keywords = mt.Keywords)
--Verify data, then un-comment the commit
--COMMIT
NOTES:
This example deletes rows with duplicate 'Keywords' data. 'Keywords' is my column name.