July 2005 Archives

For a while it's been driving me barmy, trying to obtain a list of Foreign Keys which reference a particular table. Below is the end result of my hunting. No doubt the code could be optimised, which is something I should do. As it uses the INFORMATION_SCHEMA views, it is at least SQL Server version independent and thus should work on both 2000 and 2005. (Not tested on SQL Server 7)

I'm sure there's probably a way to do this in SQLDMO, which will be my next investigation. Watch this space!

IF EXISTS (SELECT name FROM sysobjects WHERE name = N'GetFKsForTable' AND type = 'P') DROP PROCEDURE GetFKsForTable

GO

CREATE PROCEDURE GetFKsForTable
@dbname sysname,
@sTable varchar(255)
AS

DECLARE @sCurrentDB varchar(255);

SELECT @sCurrentDB = SD.name
FROM master..sysprocesses SP
INNER JOIN master..sysdatabases SD
ON SP.dbid = SD.dbid
WHERE SP.spid = @@SPID

IF LOWER(@sCurrentDB) <> LOWER(@dbName)
BEGIN
RAISERROR
('TARGET database is not CURRENT database. Aborted', 18, 1) WITH NOWAIT
RETURN
END

SELECT
CCU.TABLE_NAME AS 'FK_ORIGIN_TABLE',
CCU.COLUMN_NAME AS 'FK_ORIGIN_TABLE_COL',
CCU2.TABLE_NAME AS 'FK_OWNING_TABLE',
RC.CONSTRAINT_NAME AS 'FK_NAME',
CCU2.COLUMN_NAME AS 'FK_COLUMN'
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON CCU.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2
ON CCU2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
WHERE
CCU.TABLE_NAME = @sTable
GO

If you come across a column that claims its replicated but isn't....

PRB: Error 4928 Occurs When You Try to Rename a Non-Replicated Column

[via scally.net]

epitonic.com looks like a very cool website I'll have to spend some more time looking at. For example, a song I'd almost completley forgotten about having not heard in aaaages! Remember me.

Advanced Concepts to Prevent SQL Injection
Creating a Dynamic Configuration Dialog in C# and .NET
Tip Of the Day Dialog

Tip-Of-The-Day, I'm so determined that the product I work on should have one of these, customised so that it shows a different set of messages depending on the user logged in. I can think of several sets of messages I'd use! ;-)

Memo to self, read these:

How to Write High-Performance C# Code
T-SQL Coding Standards

Note to self: Must get round to going to this restaurant.

Sour note over new music maestro

An example of misongynism at its worst. The mind boggles at the sheer lunacy of this. One quote is "Another [member of the orchestra], who had sat on the search committee, said their artistic expertise was being disregarded."... Artistic expertise? More like male pride.

A couple of articles about "AJAX":

• Ajax: A New Approach to Web Applications


• ASP.NET Home: Life Without Refresh

Despite the name of the second article, only a small part of it is asp.net specific, with the vast majority of it being usable as a very good introuduction to the AJAX concept.

The Optimus Keyboard is quite possibly the nicest piece of kit I've ever seen. Unfortunately it's not available yet and according to the website isn't likely to see production before 2006.

I want, I want!!

[via: plasticbag.org]

Update:
The so-called Ultimate Keyboard - Quite an interesting entry about the keyboard.

I came across the No2ID Pledge via theregister.co.uk, and signed it. I don't have any concrete reasons why, but I find the idea of an ID card scheme thoroughly objectionable. Having worked in the licensed trade, I can see situations where it would be beneficial, but am at a loss as to why the existing forms of identification would be any less sufficient.

Bah. Are they trying to poison me!?!? They have a new Ham & Emmental Club sandwich which sounds like it might be quite nice. Wrong. The "mustard mayo" in it is so overpowering that the sandwich tastes of nothing BUT mustard. Won't be buying that again!

Well, the queues in WHSmith were nothing like I'd expected them to be - only 4 people waiting at each till. Time to go and lie outside in the sun and see if this book's any good. I'm hoping it maintains the story arc, or I'll be really rather annoyed.

Note to self: Probably should have re-read previous book to get up to speed.

UPDATE: (18/07) Good book, could've done with being longer though. Now I've got to wait for the next one to come out!

Not read the whole article yet, but this one about electronic paper sounds interesting.

And people wonder why user interface consistency is so difficult to achieve..... What are SYSTEM_FONT and DEFAULT_GUI_FONT.

In everything I've produced, software wise, I've at least tried to keep a consistent look and feel from window to window, with particular emphasis on using a very small number of different sized windows. One of the things that annoys me the most, which is probably why I'm particular about it, is software that uses many, many different sized windows. If a system has a reason to have multiple windows open, then most - if not all - should be of the same size or set of sizes. Ideally these windows should all have consistent branding and layout. Including font faces used, navigation and work-flow. Anything else is just damn confusing.

A couple of good articles regarding the art of managing email. [via: Jonathan Hardwick: Shipping Version One]

• Tips for Mastering E-mail overload
  
• Change This :: The Tyranny of Email
  

Potentially the best line is in the first article and has to be:

One CEO I've worked with charges staff members five dollars from their budget for each e-mail she receives. Amazingly, her overload has gone down, the relevance of e-mails has gone up, and the senders are happy, too, because the added thought often results in them solving more problems on their own.

Quite how I got to here, I'm not sure. But, it made me think about the ways of writing "good" defect reports. For a Tester/QA person to write a good defect report, they have to be able to describe how the problem they've encountered occured and the exact steps to reproduce it.

Keeping a Lab Notebook style record seems like a pretty good way to do that.

A selection of useful DBCC commands for SQL Server. Not looked in any great detail, but it seems to cover the generally useful ones.

To see just how easy it is to create a HttpModule, take a look at this entry from the blog of Wallace B. McClure. Not that I know a lot about HttpModules, but, I can't see any reason for the existence of the app_CompleteRequest routine, other than to maybe satisfy the requirements of the Interface, but then it's inconsistent with how he's treated app_EndRequest,...

Might have to give it a go at some point!