Deze post is geïmporteerd van de oude blog en is nog niet geconverteerd naar de nieuwe syntax.
When using the Windows Search functionality, you can search for words inside files. By default this doesn't seem to work for .sql or .php files however.

To enable this, add the following key to the registry:

HKEY_CLASSES_ROOT\.sql\PersistentHandler\(Default) : {5e941d80-bf96-11cd-b579-08002b30bfeb}

This will associate the plain text filter (System32\Query.dll) to the specific file type, making it possible to search the content of the file. The reason not all extensions have this enabled by default, is due to performance considerations from Microsoft's point of view. I personally haven't noticed any visible performance problems from enabling it for SQL and PHP files.

Log off and log on again. From then on you can use the search box to search inside SQL and PHP files. A quick way to make Windows recognize the change, is by killing the explorer.exe process and starting a new one. That way you don't have to close any programs.

At pilif.ch you can find a nice tool to set the PersistentHandler to the plain text filter.

Give it a try and search for 'SELECT'.
 
Deze post is geïmporteerd van de oude blog en is nog niet geconverteerd naar de nieuwe syntax.
Some nifty SQL statements I made last week:

Firstly, listing all databases on a server.

[sql]
-- Get Databases
SELECT name FROM master.dbo.sysdatabases ORDER BY name
[/sql]

Secondly, a way to get all the user-created stored procedures from a database.

[sql]
-- Get Stored Procedures
-- Type = 'P' --> Stored Procedure.
-- Category = 0 --> User Created.
SELECT * FROM sysobjects WHERE type = 'P' AND category = 0 ORDER BY name
[/sql]

Then we can retrieve the content of the stored procedure with the following query:

[sql]
- Get Stored Procedure Content
-- Name = Stored Procedure Name.
-- Colid = Multiple lines, their sequence.
SELECT text
FROM syscomments
WHERE id = (SELECT id FROM sysobjects WHERE name = '{0}')
ORDER BY colid
[/sql]

In C# you could concatenate the returned records to get the full stored procedure content.

However, do mind the encrypted column in syscomments. The above queries work fine when this is 0. So you might want to add some checking, and in case it's encrypted you could first decrypt it and then display it. But you'll have to figure out how to do that yourself :)