Get list of changed stored procedures

Create the sp, in your database, and input from- and to-date. It will return a list of all stores procedures created and changed within that period of time in your database.


CREATE PROCEDURE sp_hlp_HentNavnePaaAendredeSPere
@StartDato DATETIME,
@SlutDato DATETIME
AS
select
'Name' = o.name,
o.create_date,
o.modify_date,
'Owner' = user_name(ObjectProperty( object_id, 'ownerid')),
'Object_type' = substring(v.name,5,31)
from sys.all_objects o, master.dbo.spt_values v
where
o.type = substring(v.name,1,2) collate database_default and
v.type = 'O9T' AND
substring(v.name,5,31) = 'stored procedure' AND
o.modify_date BETWEEN @StartDato AND @SlutDato
order by
o.modify_date desc,
Object_type desc,
Name asc

Free text search stored procedure

Ever wanted search the stored procedures for a specific text, such as a table name or a text tag ?This is impossible in Management Studio, so here follows an example of how to do this..

create procedure hlp_FindTxt
@Instr nvarchar(50)
as
SELECT object_name(sc.id), 'First occurrence of:' + @InStr + ' ...' + substring(sc.text,CHARINDEX(sc.text, @Instr) - 50 ,100) + '...'
FROM syscomments sc inner join sysobjects so on sc.id = so.id
WHERE
sc.text LIKE '%' + @Instr + '%' AND
so.type = 'P' AND
so.name NOT LIKE 'dt_%'
go

Word count

Words in a column are easily counted using the splendid example I picked up on the net.

SELECT(LEN(column) - LEN(REPLACE(column, ' ', ''))) + 1
FROM table

Pretty cool snipplet to list tables with space used on disk:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14860

Very useful

Autocomplete

What is possibly the worst SQL partykiller ? Answer: Long field names in tables with long tablenames (including several spelling errors).
When I do a LEFT string operation, is the string the first or the second argument ? I can never remember, though I have used it a million times :o(

I have found this tool : http://www.promptsql.com/ VERY helpful i providing autocomplete to my SQL coding. I does it all !! Currently in RC1 for version 1.3 it is actually quite stable and cost only 25$ for a single user licence. It is HIGHLY recommended !

Insert if not exists...

Doing integration tasks, you often come about the issue of transferring all "new" records from one table to another. The easiest and fastest way to do this is shown below.

Insert into TblA(TblA_Key, FldB)
select
Fld1,
...,
...
from TblB b
where not exists ( select null from TblA a where a.TblA_Key = b.TblB_Key)

Datetime conversions

Ever tried to do a date conversion on two date fields, where you wanted to compare the date only ? Ever waited forever for the query to complete ?

Fear not ! The answer i right here:

Usually one would do the conversion by converting the two datetime fields to strings, and strip the time part:

left(convert(varchar(30),SomeDateTime,120),10) =
left(convert(varchar(30),OtherDateTime,120),10)

However, this will induce an internal conversion in SQL Server, effectively converting all records for both datetime fields to strings. Furthermore the LEFT string operator will take quite some effort til calculate, of you are handling +10 million records.
The alternative suggested by the article (link below) gives quite another expression:

DATEADD(d,DATEDIFF(d,0,SomeDateTime),0) =
DATEADD(d,DATEDIFF(d,0,OtherDateTime),0)

This statement does not induce a full table scan, and will therefore make use of an index (if one exists) on the datetime fields.

I was able to speed up a query that took 40 seconds, down to less than 1 second by this method.

Follow the white rabbit to the source of all SQL Server knowledge..
(www.sqlserverperformance.com)

SQL Server 2005 - missing query tool for tablenames - a solution

It pissed me off, BIG time... Having installed a less-than-perfect first official release of SQL Server 2005 client tools. It didn't even have intellisense on sql queries ! Furthermore the functionlality of good old Query Analyzer where you could search for database objects was not included.

As a workaround, I have made this simple stored procedure that will do a search on any object in the database, containing a part of the input string in its name. Simply put the stored procedure in any database you might want to query on database objects on a regular basis.


create procedure st
@Sstring varchar(50)
as
SELECT
name,
CASE
WHEN xtype = 'U' then 'table'
WHEN xtype = 'P' then 'Stored Procedure'
END as type
FROM
sysobjects
WHERE
xtype in ('U','P')
AND name like '%' + @Sstring + '%'
Order By name
GO


xtype='U' are all user tables.
xtype = 'P' are all stored procedures.

Have fun !