Get the Names of Stored Procedures that Reference a Given Table in SQL Server

SQL Server logo

One of my colleagues sent me a very handy bit of code this morning that I thought would be helpful to post.

Below are two options which, when run against the database, should return the names of all stored procedures that reference the named table.

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

I am told that the functionality of the first option has been verified.

Moving Data from the IMAGE Datatype to VARBINARY(MAX) from a SQL Server 2000 Instance

SQL Server logo

I am in the process of writing scripts to move data from a SQL 2000 database into a different database running on SQL 2008.

I had set up a linked server from the SQL 2k server to begin this process, and about 90% of the data could be moved.  One of the last tables to be migrated was one that held file attachments. I ran into a problem when I realized that the SQL 2k table had an IMAGE column, and the corresponding SQL 2k8 table used a VARBINARY(MAX) column.

The SQL2k database has a table with the following definition:

CREATE TABLE [dbo].[tOldTable](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [DocName] [varchar](300) NOT NULL,
 [DocData] [image] NOT NULL,
 [DocContentType] [varchar](300) NOT NULL,
 [User] [varchar](50) NOT NULL,
 [Date] [datetime] NOT NULL,
 [requestId] [int] NOT NULL,
 [requestTypeId] [int] NOT NULL)

The new table, in the SQL2k8 database, has this structure:

CREATE TABLE [dbo].[tNewTable](
 [SupID] [int] IDENTITY(1,1) NOT NULL,
 [ProjectID] [int] NOT NULL,
 [FileName] [varchar](250) NULL,
 [FileExtension] [varchar](25) NULL,
 [MIMEType] [varchar](250) NULL,
 [FileData] [varbinary](max) NULL,
 [Thumbnail] [varbinary](max) NULL,
 [FileSize] [int] NULL,
 [Width] [int] NULL,
 [Height] [int] NULL,
 [FileModificationTimestamp] [datetime] NULL,
 [CreatedBy] [varchar](100) NULL,
 [CreateTimestamp] [datetime] NULL,
 [Removed] [bit] NULL)

At one point, I was planning on using a CURSOR to load the data from the first table into a table variable and explicitly CAST it as VARBINARY(MAX), but then I re-discovered the fact that the IMAGE datatype cannot be stored in a local variable.  My second problem – even if the first step had worked – is that VARBINARY(MAX) is not available on SQL 2000 (it was introduced in SQL 2005), and would cause an error if run from the SQL 2k server.

After refactoring and improving the code, I had removed the CURSOR, but was still uncertain as to how I could move this data.  I decided to JOIN the SQL 2k table with an existing table variable, and directly INSERT the rows like so:

INSERT INTO [SQL2k8].[NewDB].[dbo].[tNewTable]
 ([ProjectID]
 ,[FileName]
 ,[FileExtension]
 ,[MIMEType]
 ,[FileData]
 ,[FileModificationTimestamp]
 ,[CreatedBy]
 ,[CreateTimestamp]
 ,[Removed])
SELECT tv.[ProjectID] AS [ProjectID]
 ,[DocName] AS [FileName]
 ,RIGHT([DocName],3) AS [FileExtension]
 ,[DocContentType] AS [MIMEType]
 ,[DocData] AS [FileData]
 ,[Date] AS [FileModificationTimestamp]
 ,[User] AS [CreatedBy]
 ,[Date] AS [CreateTimestamp]
 ,0 AS [Removed]
 FROM [dbo].[tOldTable] ot
 JOIN @tTableVariable tv ON tv.[requestID] = ot.[requestId]

This was an implicit conversion from IMAGE to VARBINARY(MAX), but since the insertion took place on the SQL 2008 server and the code never mentions the datatype (which would not have been recognized by the SQL 2k instance), no errors resulted. Problem solved!

Incidentally, the IMAGE datatype has been deprecated, and should be converted to VARBINARY(MAX) wherever possible.

Acceptance into Project Glass Pilot Program!

acceptance into Glass Explorers

I received a message today on Google+ย indicating my acceptance into the Project Glassย program! ย Apparently 8000 people have been selected to be the first allowed to buy it. ย Now I’m just waiting for the next message…

#glassexplorers