Finding column references in SQL Server

Here is a stored procedure that will generate a text formatted report that details what views, stored procedures, functions and triggers use the specified column. This is useful to know when you make a change to a column. This information is available from the UI, but this is a nice SQL way of doing it. This solution was copied from http://www.sqlservercentral.com/scripts/Miscellaneous/31963/. Thank you!

if exists (select * from dbo.sysobjects
 where id = object_id(N'[dbo].[usp_FindColumnUsage]')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_FindColumnUsage]
GO

CREATE PROCEDURE [dbo].[usp_FindColumnUsage]
 @vcTableName varchar(100),
 @vcColumnName varchar(100)
AS
/************************************************************************************************
DESCRIPTION: Creates prinatable report of all stored procedures, views, triggers
  and user-defined functions that reference  the
  table/column passed into the proc.

PARAMETERS:
  @vcTableName - table containing searched column
  @vcColumnName - column being searched for
REMARKS:
  To print the output of this report in Query Analyzer/Management 
  Studio select the execute mode to be file and you will
  be prompted for a file name to save as. Alternately
  you can select the execute mode to be text, run the query, set
  the focus on the results pane and then select File/Save from
  the menu.

  This procedure must be installed in the database where it will
  be run due to it's use of database system tables.

USAGE:
  
  usp_FindColumnUsage 'jct_contract_element_card_sigs', 'contract_element_id'

AUTHOR: Karen Gayda

DATE: 07/19/2007

MODIFICATION HISTORY:
WHO  DATE  DESCRIPTION
---  ---------- -------------------------------------------
*************************************************************************************************/
SET NOCOUNT ON

PRINT ''
PRINT 'REPORT FOR DEPENDENCIES FOR TABLE/COLUMN:'
PRINT '-----------------------------------------'
PRINT  @vcTableName + '.' +@vcColumnName

PRINT ''
PRINT ''
PRINT 'STORED PROCEDURES:'
PRINT ''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Procedure Name]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE = 'P'
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [Procedure Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures for column "' + @vcTableName + '.' +@vcColumnName +  '".'

PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''
SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [View Name]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE = 'V'
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views for column "' + @vcTableName + '.' +@vcColumnName +  '".'

PRINT ''
PRINT ''
PRINT 'FUNCTIONS:'
PRINT ''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Function Name],
  CASE WHEN o.XTYPE = 'FN' THEN 'Scalar'
   WHEN o.XTYPE = 'IF' THEN 'Inline'
   WHEN o.XTYPE = 'TF' THEN 'Table'
   ELSE '?'
  END
  as [Function Type]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE IN ('FN','IF','TF')
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions for column "' + @vcTableName + '.' +@vcColumnName +  '".'

PRINT''
PRINT''
PRINT 'TRIGGERS:'
PRINT''

SELECT DISTINCT  SUBSTRING(o.NAME,1,60) AS [Trigger Name]
  FROM sysobjects o
  INNER JOIN syscomments c
   ON o.ID = c.ID
  WHERE  o.XTYPE = 'TR'
   AND c.Text LIKE '%' + @vcColumnName + '%' + @vcTableName + '%' 
  
  
 ORDER BY  [Trigger Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers for column "' + @vcTableName + '.' +@vcColumnName +  '".'

GO

Adding AJAX.Net to existing ASP.Net Web Site

If you are like me you have web sites in ASP.Net that don't use AJAX.Net and when the web site was created there was no such thing as an AJAX-enabled project in Visual Studio 2005. With the release of AJAX.Net v1.0 of AJAX is a compelling set of functionality that I want to take advantage of. It is trivial when it comes to creating a new web site in Visual Studio 2005. Just select the AJAX enabled web site when you create your web site. However, the question I needed an answer to was how do I AJAX enable my existing web sites that are not AJAX enabled. The easy answer I found is create a new web site in Visual Studio 2005. Open the Web.config. Here you will see a lot of new sections and tags. Just copy all the new stuff (which is all except a couple of tags like assemblies and compilation tags) to your web config. That should do it. You can now play with AJAX.Net 1.0 in your existing web site. As a bit of background, I tried just dragging the ScriptManager and UpdatePanel controls to my page. The behavior was that it posted back still and also refreshed the entire page. Weird I thought to myself. Then I realized there was a Javascript error that said 'Sys' is undefined Doing a quick search on Google I quickly realized this was because needed to tell my web site about AJAX.Net so the AJAX.Net control would have the client side javascript they were expecting. This is the simplest solution I know. It has the advantage of always referencing the correct assemblies and versions, etc.

Conditional Compilation to run Windows Service as an application

Conditional Compilation is a concept that has been around since C and C++. The idea is that you can define variables, control flow, etc for what gets compiled and what does not.

This concept can be illustrated by looking at testing of Windows Services in Visual Studio 2005. It is time consuming and just no fun to write code as a Windows Service as it cannot be run directly in Visual Studio. The solution I propose is to use Conditional Compilation to allow the Windows Service to be ran as a Windows Service or Windows Form Application. To do this, see below. // NOTE: Add RunAsApp to Conditional Compiler Symbols under the Build tab on the Project properties. #if (RunAsApp) // To test as an app MyServicee app = new MyService(); Application.Run(app); #else // To test as a Service ServiceBase[] ServicesToRun; ServicesToRun = new ServiceBase[] { new MyService() }; ServiceBase.Run(ServicesToRun);

Very efficient paging in SQL Server 2000 or 2005 without temp table

What I mean by paging in SQL Server is something like this. Assume you have a table that has a million rows. You want to show the user only a portion of those records such as through a web interface. The user could then click next page / previous page to see more results. You may also show the user a list of pages so they can jump to any page without clicking next/previous links. With that said, most solutions for SQL paging stored procedures involve a temp table for SQL 2000 and using ROW_NUMBER() function in SQL 2005. While the SQL 2005 solution is pretty easy it doesn't work in SQL 2000. The articules imply that this solution is better from a performance standpoint, I found it to be the opposite, so your mileage may vary. I recommend if you have SQL 2005, then use the ROW_NUMBER() function as the code clean, and easy to understand. Below is a very technical solution, but it is not difficult to adapt to your situation, and it works on both 2000 and 2005. I am not going to try to explain it here as it is explained in great detail here: http://www.4guysfromrolla.com/webtech/042606-1.shtml. BTW, thank you to author for this solution. The math required to convert a zero-based page index to a value that can be used for the @startRowIndex parameter is as follows, and could be in your .net code, or whatever. int startRowIndex = Math.Ceiling(pageIndex * pageSize); // NOTE: you will need to do some data type conversions, but this is basically it. The parameter @startRowIndex is 1 based. The parameter @maximumRows is essentially how many records you want in a page. CREATE PROCEDURE [dbo].[GetEmployeesPaged] ( @startRowIndex int, @maximumRows int ) AS DECLARE @first_id int, @startRow int -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that -- Get the first employeeID for our page of records SET ROWCOUNT @startRowIndex SELECT @first_id = employeeID FROM employees ORDER BY employeeid -- Now, set the row count to MaximumRows and get -- all records >= @first_id SET ROWCOUNT @maximumRows SELECT e.Name, e.EmpoyeeID FROM employees e WHERE employeeid >= @first_id ORDER BY e.EmployeeID SET ROWCOUNT 0 GO The solution above is the best performance wise for a SQL 2000 database. I recommend if you have SQL 2005 that you use the ROW_NUMBER() function as shown below. CREATE PROCEDURE [dbo].[GetEmployeesPaged2005] ( @startRowIndex int, @maximumRows int ) AS -- A check can be added to make sure @startRowIndex isn't > count(1) -- from employees before doing any actual work unless it is guaranteed -- the caller won't do that SELECT * FROM (SELECT e.Name, e.EmpoyeeID ROW_NUMBER() OVER(ORDER BY employeeid) as RowNum FROM employees e ) as DerivedTableName WHERE RowNum BETWEEN @startRowIndex AND (@maximumRows + @startRowIndex - 1) SET ROWCOUNT 0 GO

Reflection makes partial classes visible again

ASP.Net 1.x didn't use partial classes to implement pages and user controls. This made things a little cluttered. However, it had the advantage of the type being accessible for things like casting the Page object to your own specific page class. You can't do this in ASP.Net 2.x because partial classes are not available until runtime when the two partial classes are joined to make a complete class. This means we can't cast. You might be thinking, why would I want to do that anyway. In most cases you don't have a big need. However, when you want to pass information between a user control a master page or even the page itself it can be difficult. Let's assume you have a user control as defined below. public partial class MyControl : System.Web.UI.UserControl { public string MyProperty { set { myLabel.Text = value; } } } In ASP.Net 1.x (assuming MyControl was defined as a non-partial class because 1.x didn't support partial classes) you do something like the following from the parent Page of this control. The following assumes that the parent page has an instance of the MyControl on the page called myControl. public class MyPage .... ((MyControl)this.myControl).MyProperty = "abcdefg"; .... In ASP.Net 1.x the compiler would know how to find MyControl class and would compile ok. In ASP.Net 2.0 the compiler would NOT know how to find MyControl class and would FAIL to compile. Again, the reason is the class is a partial class and does not exist yet. How do we fix this you ask? Option 1. Convert your Web Site project to Web Application Project under ASP.Net 2.0. Option 2. Convert only this class to a non-partial class. I have not tried this, and don't know if this will work. Option 3. Use reflection to get around the issue. This works well since both reflection and partial classes will be doing their magic at runtime, not compile time. /// <summary> /// Set the value of a property on any class /// </summary> public static void SetProperty(System.Object obj, System.String propertyName, System.Object propertyValue) { if (obj == null) { throw new System.ArgumentException("No target specified"); } if (propertyName == null) { throw new System.ArgumentException("No name specified"); } PropertyInfo pi = obj.GetType().GetProperty(propertyName); if (pi == null) { throw new Exception("Object does not have a property named: " + propertyName); } pi.SetValue(obj, propertyValue, null); } Be warned, reflection can open up the chance for errors that the compiler can no longer check for. For example, don't change MyProperty, to MyProperty2 without changing your string in the SetProperty method above. Now we can do something like:

....

SetProperty(myControl, "MyProperty", "abcdefg");

....

Also, try not to do something like this in a loop as there is a slight performance hit for using reflection.

Disable MS Office 2003 clipboard ring

If you don't know what I mean, go into any Office product since 2000 and type Control-c twice. You will get an annoying pane that steals all your valuable real estate and keeps coming back. I have not figured out how to disable it completely. The Office XP and Office 2000 fixes don't seem to affect the clipboard ring in Office 2003. What I have been able to do is make it so it does not bother me anymore. When it comes up, go to the options list and uncheck every option there. Basically, tell it to never show itself, never come up, never automatically do anything. It seems to be a global setting that affects all Office products with just one change. I hope this gives someone some sanity.

How to optimize HP OpenView Service Desk web-api calls

Open HP OpenvView Service Desk (OVSD). Go to the System administration module. In the tree select Data and then Web Api Application. Add a new item and give it a name. This is the name you will reference in your code. Add the attributes you will be using in your code. It is important to include as many of the columns as possible. Otherwise, they will be loaded on demand when you access them and this takes more time. Now that you have a Web Api Application defined in OVSD use the following code to use it in your code. public void SetWebApiApplication(ApiSDSession session, String appName) { IWebApiApplicationWhere where; IWebApiApplication[] applications; IWebApiApplication appl1; // Find the application mentioned in the argument. IWebApiApplicationHome applicationHome = session.getWebApiApplicationHome(); where= applicationHome.createWebApiApplicationWhere(); where.addCriteriumOnText(appName); applications = applicationHome.findWebApiApplication(where); if (applications == null) { System.out.println("There is no Web Api application called " + appName); return; } appl1= applications[0]; session.setApplicationSettings(appl1); } Essentially, when you use Web Api Application it is like doing the following in SQL. select col1, col5, col34 from MyLargeTable instead of select * from MyLargeTable You may not realize it, but I think OVSD also uses a "select" to do an update of data as well. The reason for this conclusion is that you must still search for the record you want to update, load the data into memory, make the modification, and then write change back to database. With that said, the biggest performance gain is going to be when you bring back many records instead of just one. There is still a performance gain for one or two records, but it is negligible in most cases because you have to specify the Web Api Application before you do the actual query.