Month: February 2009

This will list stored procedures, views, etc. that uses a given table column:

SELECT OBJECT_NAME(sd.id) Dependent_Object,
      (SELECT xtype FROM sysobjects so WHERE so.id = sd.id) Object_Type
FROM sysobjects so
INNER JOIN syscolumns sc ON so.id = sc.id
INNER JOIN sysdepends sd  ON so.id = sd.depid and sc.colid = sd.depnumber
WHERE so.id = OBJECT_ID('tblcategorypage')
AND sc.name = 'categorytype'

Credits for this tip to SQLServerCentral.

/Emil

You won’t need them very often but it might be good to know that they’re there. Here’s an example of how to use a bitwise AND in a where clause:

SELECT * FROM uppdrag WHERE (applikationer & 1) > 0

In this example, ‘applikationer’ is a column containing several combined flags and we want to check the setting on the flag in the lowest bit.

The bitwise operators are:

• & – bitwise AND
• | – bitwise OR
• ^ – bitwise EXCLUSIVE OR

Have you ever had a need to create temporary database tables in a SQL Server session or procedure? Are you used to creating them by hand like this:

CREATE TABLE #tmp
( 
    id INT, 
    name VARCHAR(32) 
)

Depending on what you need the table for, then there might be an easier way. Have a look at this short session:

SELECT * INTO mytemptable FROM foobar
SELECT * FROM mytemptable
DROP TABLE mytemptable

The first line creates a new temporary table based on the result set of a SELECT query, and stores the result set in the table. The query can be more complex than in the example, with JOINs, WHERE clauses etc. Incredibly useful when mangling and transforming data!