Sql Server Formatter - Free and Useful

http://architectshack.com/PoorMansTSqlFormatter.ashx#Download_8 --> Download SSMS Plugin

http://poorsql.com/ --> Free Online Formatter

 

Trick: Row Count for larger tables

http://brian.chipsofttech.com/blogs/brian/post/SQL-Server-Row-Count-for-Large-Tables.aspx

Twist in ISNULL function - Interesting

http://www.sqlservercentral.com/articles/T-SQL/76861/

Trim Leading and Trailing quotes before inserting to database - Sql Server

Importing from a excel to SSIS is a tedious task. Some time we have to convert from excel to tab seprated text file and then import.

While converting from excel to tab separated file "quotes" get appended to the text.

Following is a Sql function to remove leading and trailing quotes from text and then insert to db.

[code language="sql"]</pre>
CREATE FUNCTION dbo.TrimQuotes(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT, @End Int, @Length INT
SET @Str = REPLACE(RTRIM(LTRIM(@Str)),'""', '"')
SELECT @Start = CHARINDEX('"', @Str), @End = CHARINDEX('"', REVERSE(@Str)),
@Length = LEN(@Str)
IF @Start =1
SET @Start = @Start + 1 -- new string must start from second character
ElSE
SET @Start = 1 --as there is not starting quote. we will not trim it
IF @End != 1
SET @End = 0 -- as there is not ending quote. we will not trim it

SELECT @Str = SUBSTRING(@Str,@Start, @Length - @Start - @End + 1)

RETURN @str

END
GO
SELECT dbo.TrimQuotes ( '"abc"') , dbo.TrimQuotes ( ' abc" ') , dbo.TrimQuotes ( '"a"b"c') , dbo.TrimQuotes ( 'a"b"c')
, dbo.TrimQuotes ( '"abc'), dbo.TrimQuotes ( '"a""bc'), dbo.TrimQuotes ( '"""b""c""')
<pre>
[/code]

Job to list all the Sql Server Agent Jobs

[code language="sql"]

with JobStatus as

(

select

job_id AS JobId,

row_number() over(partition by job_id order by run_date desc, run_time desc) asExecutionRank,

case run_status when 0 then 'Failed' when 1 then 'Succeeded' when 2 then 'Retry' when3 then 'Canceled' end as LastRunStatus

from

msdb.dbo.sysjobhistory

)

select

j.job_id as JobId,

j.name as JobName,

j.[description] as JobDescription,

j.[enabled] as IsJobEnabled,

js.LastRunStatus,

ja.run_requested_date as LastRunDate,

ja.next_scheduled_run_date as NextRunDate,

datediff(second, ja.start_execution_date, ja.stop_execution_date) as JobDuration

from

msdb.dbo.sysjobs j

join msdb.dbo.sysjobactivity ja on j.job_id = ja.job_id

left join JobStatus js on j.job_id = js.JobId

and js.ExecutionRank = 1

order by

JobName,

NextRunDate
[/code]