Helper Queries

Here are some miscellaneous queries I've written and/or found that can be pretty helpful when you need to find something out or do something and don't want to take the time examining the BOL or system tables. If you have any others send them to me and I'll include them here.

Unless it says otherwise you can run these in Query Analyzer by selecting the database you want them to run in and then copying and pasting them into the query window. The items you need to change for your specific query are in bold.

---

List all of the column names in a table

This query will list all of the colums in the table you enter. Ok, sure you can do this any number of other ways, but this is nice if you need to get a quick list to stick in an email or somewhere else. Just change the execute mode to "Results in Text".

SELECT syscolumns.name 
FROM syscolumns INNER JOIN L
ON syscolumns.id = sysobjects.id 
WHERE o.name ='tableName'
			

---

Search stored procedures for a string

Ever need to find all of the queries in a database that access a specific table? Just enter the table name, or whatever else you're looking for, in the query below and run it against your database.

SELECT sysobjects.name, text 
FROM sysobjects INNER JOIN syscomments 
ON sysobjects.id = syscomments.id 
WHERE text LIKE '%searchString%'
			

---

If you have a comment, better way to do it, or correction let us know comments@directedinsight.com