How To INIT CAP Data In T-SQL



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)

How To Check @@ERROR to Determine If An Error Occurred In A Stored Procedure



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.

How To Reseed An Identity In SQL Server Using T-SQL



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.

How To List All User Tables In SQL Server



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

How To Get Information On SQL Maintenance Plans Job Steps



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.

How To Find Text That Exists In A Stored Procedure, Function Or Trigger



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.

How To Create A Table From A SELECT Statement



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.

How To Find Or List All Column Names and Information For A Table Using T-SQL



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.

Re-Identity a Table in SQL Server



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.

Delete Duplicate Records From A Table



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.