Search all columns of all tables in a databse

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

What I learned today...

Today I learned that you don't leave a loose can of sprite in your hot car trunk all day and then drive home.

How to group some results at the top...

Note: I know the spacing on this post is kinda crazy. Don't have time to fight with blogger's editor right now, so just deal, okay?

Let's say you want to get a list of things back from a query, grouping a particular group of items at the top of your query, and the rest below.

For example, let's say you're getting back a contact list sorted alphabetically by organization, and THEN by contact's last name. Let's say you want all of those that belong to "RandCorp" to show at the top because you're interested in those first. Normally, you could sort by "org" and then by "lastname". Unfortunately, RandCorp comes after AAA Coating in the alpha sort. But what you really want is the randCorp items to be listed first (cause those are the ones you're most interested in, but you also want to see the others).

Here's one way to do it.

SELECT LastName, Org,
CASE WHEN Org = 'RandCorp' THEN 1 ELSE 0 AS keySort
FROM Clients
ORDER BY keySort DESC, Org, LastName

If your data looked like this:

LastName	Org
Myers	Zenith Tech
Radner	AAA Coating
Ferrel	RandCorp
Farley	Zenith Tech
Murphy	AAA Coating

This query would return:

LastName	Org
Ferrel	RandCorp
Murphy	AAA Coating
Radner	AAA Coating
Farley	Zenith Tech
Myers	Zenith Tech

How many records were updated with my CF update query?

Thanks to Mike O. for finding this:

SET NOCOUNT ON;
BEGIN TRANSACTION;


UPDATE tblApplicant
SET frozen = 1
WHERE ...

SELECT @@rowcount AS changes;

COMMIT TRANSACTION;
SET NOCOUNT OFF;

Another way to get the identity of a new record

set nocount on

Declare @NewPersonnel_ID int

insert into tbl_Personnel
(
Personnel_Status_ID,
Name_First,
Name_Middle,
Name_Last,
Display_Contact_Us,
Personnel_Version_Info,
Creation_user,
Creation_Date
)
VALUES (
58, -- Check to see if 58 is the right choice. [looks it up] It is the right choice: (active)
'Leon',
'J.',
'Leach',
0,
'[eheard] Added manually per bug 2179',
115,
'3/29/2007'
)

set @NewPersonnel_ID = (SELECT @@IDENTITY)

set nocount off

SQL Reference

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbrfen9/dbrfen9.htm

Example of looping through query results AND conditional statements (dupe checking)

-- Get pubs related to program 11 and 29
DECLARE @pub_id int
DECLARE @dupe_count int
DECLARE pub_cur CURSOR
FOR select publication_id from tbl_Programs_To_Publications where program_id in (11,29)
OPEN pub_cur
FETCH pub_cur INTO @pub_id
-- loop through pubs
WHILE(@@FETCH_STATUS=0) BEGIN -- Here's a WHILE loop
-- check for existing xref
set @dupe_count = (
select count(program_publication_id)
from tbl_programs_to_publications
where program_id = 58 and publication_id = @pub_id
)
if @dupe_count = 0
-- insert xref for LAI
INSERT INTO tbl_Programs_To_Publications
(
program_id,
publication_id,
creation_user,
creation_date
)
VALUES (
58,
@pub_id,
115,
5/18/07
)

-- get the next record
FETCH pub_cur INTO @pub_id
END
CLOSE pub_cur
DEALLOCATE pub_cur