Category Archives: General

Searching for text in MSSQL stored procedures

Simple 5 minute tip here, but I’d done this ages ago and forgotten how to do it.

Suppose you know somewhere in the multitude of stored procedures in the database you’ve inherited that there’s likely a reference to a field or table, and you don’t know where it is. How do you find it without going through each stored procedure and checking it?

Easy. The definitions are stored in the system tables, just like pretty much all structure of a MSSQL database, so we just take a look in those tables, or the INFORMATION_SCHEMA views.

There’s a few different ways of getting at the information, but let’s say you’re looking for references to the field email, and the table Organisation:

SELECT		routine_name, 
		created, 
		last_altered, 
		routine_definition 
FROM		information_schema.routines
WHERE		routine_definition LIKE '%email%'
AND		routine_definition LIKE '%Organisation%'

SELECT		NAME, 
		create_date, 
		modify_date, 
		OBJECT_DEFINITION(OBJECT_ID) as Routine_Definition
FROM		sys.procedures
WHERE		OBJECT_DEFINITION(OBJECT_ID) LIKE '%email%'
AND		OBJECT_DEFINITION(OBJECT_ID) LIKE '%Organisation%'

or all references to a different email field that include no reference to the Organisation table? This one’s a bit more likely give false negatives – e.g. the procedure may reference the Organisation table in some way not related to the email field. But you get the idea.

SELECT		routine_name, 
		created, 
		last_altered, 
		routine_definition 
FROM		information_schema.routines
WHERE		routine_definition LIKE '%email%'
AND		routine_definition NOT LIKE '%Organisation%'
Advertisements

Leave a comment

Filed under General

Moving from VMWare Server 2 to VMWare ESXi 4.1 – a walkthrough

I’m sitting here gradually migrating a bunch of VMs on to our new server running VMware vSphere Hypervisor Based on ESXi. It’s taking a little while, and I got a bit flummoxed at a couple of points so I thought I might document what I had to do in order to get it all working.

There’s nothing really complex here, but I found it confusing in a number of places, so hopefully this may be of use to others – it will certainly be a memory aid for me in the future.

Background:

We have an old Windows server the free running VMware Server 2 administered via VMware Infrastrutcure Web Access. There are some peculiarities if you want to run a console window in Firefox, but it generally works well, and is free, so I can’t really complain.

Whilst we’re doing this migration, we’re also looking at consolidating software licenses, an obvious candidate is the host machine running VMs – in this case I could either run VMware Server on Windows or Linux, or look at VMware vSphere based on ESXi. I’ve run VMware Server 2 on Ubuntu before – it was a bit of a nightmare and took a couple of days to get working properly, so I was keen to see if ESXi was any better. ESXi also runs bare metal as opposed to on top of another operating system, so there should be some performance benefits too.

So, VMware vSphere ESXi it is then.

All of the VMware software we’re using below is free, but you’ll have to register in order to download it.

Continue reading

2 Comments

Filed under Virtualisation