Delete all Stored Procedures at once

If this is a one- time task, just open Object Explorer, expand your database > programmability and highlight the Stored Procedures node. Then turn on Object Explorer Details. On the right you should see your list, and here you can multi-select - so you can sort by name, choose all procedures that start with aspnet_, and delete them all with one keystroke.

If you are doing this repeatedly, then (assuming your procedures are all in the dbo schema):


  + QUOTENAME(name) + ';
' FROM sys.procedures
WHERE name LIKE N'aspnet[_]%'
AND SCHEMA_NAME(schema_id) = N'dbo';

EXEC sp_executesql @sql;

LINQ to SQL - Enabling Fulltext searching

LINQ to SQL allows you write your data access statements in your .Net code in LINQ which is then translated into SQL. LINQ supports many of the same constructs, predicates of SQL however some aren't supported.

One set of predicates that aren't supported are those for full text, i.e. CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE That means you can't write something like,

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.Contains("Description,Title","simon")

                 select s;

You can however do

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.description.Contains("simon")

                 select s;

So you might think that full text is supported. Unfortunately Contains is translated into a LIKE predicate with two wildcards, i.e.

select *

  from sessions

 where description like '%enrico%'

So how do you get it working. Well the magic is in Inline Table Valued Functions. I've talked about them before when discussing performance of scalar functions. An inline table valued function is essentially a prameterised SQL statement, and importantly only one statement. This means the optimiser can merge the SQL Statement into the query that uses it.

To get it working you need to create a table valued function that does nothing more than a CONTAINSTABLE query based on the keywords you pass in,

create function udf_sessionSearch

      (@keywords nvarchar(4000))

returns table


  return (select [SessionId],[rank]

            from containstable(Session,(description,title),@keywords))


You then add this function to your LINQ 2 SQL model and he presto you can now write queries like.

var sessList = from s   in DB.Sessions

               join fts in DB.udf_sessionSearch(SearchText) on s.sessionId equals fts.SessionId

             select s;

If you want you can extend this to limit the number of results from containstable. If you want to search different columns then you will need different functions as the colum list used by containstable cannot take a parameter.