Finding dependencies on a table column

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

Bitwise operators in T-SQL?

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

Zune Desktop Theme for Windows XP

Tired of your old Windows XP theme? Can’t decide which color scheme is the least ugly (blue, olive green or silver)?

Then why not try the Zune Desktop Theme for Windows XP? It’s designed to resemble the look of the Zune player, so I suppose its purpose is to inspire an interest in that device. Still, it looks quite good if you ask me:

zunetheme_crop

If you’re interested, download the theme here:

http://go.microsoft.com/fwlink/?LinkID=75078

Easy way to create temporary tables in SQL Server

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!

Exclude files and folders from Web Deployment projects

Getting a lot of garbage when compiling web deployment projects? Don’t worry, there’s an easy remedy!

This is how to do it (using Visual Studio):

  1. Check out the web deployment project.
  2. Right-click the deployment project in the Solution Explorer and select “Open Project File”. The project file will be opened in the built-in Xml editor.
  3. Insert Xml code similar to the following last in the file, just before the end tag for the Project element:
    <project>
      ...
      <ItemGroup>
        <ExcludeFromBuild Include="$(SourceWebPhysicalPath)\obj\**\*.*"/>
        <ExcludeFromBuild Include="$(SourceWebPhysicalPath)\Properties\**\*.*"/>
        <ExcludeFromBuild Include="$(SourceWebPhysicalPath)\**\*.csproj*"/>
        <ExcludeFromBuild Include="$(SourceWebPhysicalPath)\**\*.resx"/>
        <ExcludeFromBuild Include="$(SourceWebPhysicalPath)\**\*.Publish.xml"/>
      </ItemGroup>
    </project>
    
  4. After this change, all files matching the wildcard expressions given in ExcludeFromBuild elements will be excluded. Problem fixed!

Good luck deploying!

Update: The double asterisks means that the path matching is done recursively in the file system. For example, to exclude everything from the “obj\” folder we have to use “obj\**\*.*” since “obj\*.*” only matches files placed directly in the folder. Files in sub-folders would not be excluded if we used that pattern.

/Emil