Handy Code by George Hernandez

Very common short bits of code

I type these over and over and yet sometimes I have to look it up!

Qualify objects as needed.

ServerName.DatabaseName.OwnerOrSchema.ObjectUsuTable

Explicitly convert data types. Usually char(n), varchar(n), nvarchar(n), int, float, datetime, and money.

CAST(Price AS int)
CONVERT(int, Price)

Local variables are handy especially in stored procedures.

DECLARE @myVariable varcar(10)
SET @myVariable = 'hi'

Use this to temporarily disable the server from returning the number of rows affected by the SQL statement (i.e. @@ROWCOUNT).

SET NOCOUNT ON

For an expression, return a default value if it's NULL.

ISNULL(Size, 'Med')

Use CASE like an inline lookup.

SELECT 
Name,
Party = CASE Name
    WHEN 'R' THEN 'Republican' 
    WHEN 'D' THEN 'Democrat'
    ELSE 'Other',
    END
AgeGroup = CASE
    WHEN Age <  12  THEN 'Child'
    WHEN Age >= 12 AND < 20 THEN 'Teen'
    WHEN Age >=20 AND < 65 THEN 'Adult'
    WHEN Age >=65 THEN 'Senior'
    END
FROM Voter

This next bit used when getting stuff before it goes into SQL. Often times you get data from a form control and use it in SQL. Two common problems are empty strings and apostrophes. Alternatively, you may want to avoid NULLS and enter empty strings, or you may want to detect the empty string and not enter anything at all (especially if the column in SQL has a default value).

//In JavaScript
function ValueForSQL(InValue) {
    if (InValue=="") return "NULL";
    else return String(InValue).replace(/'/g,"''");
}

'In VBScript
Function ValueForSQL(InValue)
    If InValue = "" Then ValueForSQL = "NULL
    Else ValueForSQL = "'" & Replace(InValue, "'", "''") & "'" 
End Function

There is also the situation where MySQL will put in a "friendly" or "soft" line break within a field when you have stuff like a \r\n in your code that is supposed to represent a Windows end of line (EOL) as opposed to be an actual EOL. This will occur in very particular circumstances. For HTML code in, say, a <pre>, try using stuff like Numeric Character References. EG:\r\n. One cheapo mnemonic for this is SQL 92.

Given Date of birth, get age

If c1 is a column/expression that holds the date of birth, then the following statement will select his/her age.

SELECT DATEDIFF(yy, c1, current_timestamp ) -
CASE WHEN DATEADD(yy,datediff(yy,c1,current_timestamp),c1) > current_timestamp THEN 1
ELSE 0
END

Get info about databases on a server

The system stored procedure of sp_databases returns name, size, and remarks about the database in an instance of SQL Server.

This SQL code provides additional info about the databases on a server:

use master
select name, crdate, filename 
from sysdatabases
order by name

Here are some system views that provide info about the databases on a SQL 2005 server:

select * from sys.databases
select * from sys.database_files
select * from sys.master_files

Get info about tables in a database

The system stored procedure of sp_tables returns info about the tables in an instance of SQL Server.

Make the following stored procedure in your database and run it from there to get info about each table in that database.

CREATE procedure spTableStats as

/* --------------------------------------------------------------------------------------------------------- This code, for the current database, lists info about each of its tables: name, row count, column count, reserved KB, used KB, used percent, category, and description. Note that the latter 2 are extended properties of each table. Created by: George Hernandez Modified: 2001 September 21 */ ---------------------------------------------------------------------------------------------------------

--Prepare variables and temp table
set nocount on --Don't say how many rows
declare @SQL nvarchar(2000), @TableName sysname, @Columns int, @Rows int
declare @ReservedKB int, @UsedKB int, @UsedPercent real
declare @Category nvarchar(300), @Description nvarchar(300)
if exists(select name from tempdb.dbo.sysobjects where name like '#tbl1%')
	drop table #tbl1
create table #tbl1(TableName sysname, Columns int, Rows int,
	ReservedKB int, UsedKB int, UsedPercent real, Category nvarchar(300), Description nvarchar(300))

--Fill cursor then populate temp table with columns from extended properties.
declare crsr1 cursor for
	select t1.TableName,t1.Columns,t1.Rows,t2.ReservedKB, t2.UsedKB, t2.UsedPercent
	from
		(select so.name as TableName, count(sc.name) as Columns, si.rowcnt as Rows
		from sysobjects as so join syscolumns as sc on (so.id=sc.id)
			join sysindexes as si on (so.id=si.id)
		where so.type='U' and so.name<>'dtproperties' and si.indid in(0,1)
		group by so.name,si.rowcnt) as t1
	join
		(select so.name as TableName, sum(si.reserved*8) as ReservedKB, sum(si.used*8) as UsedKB
		, case sum(si.reserved)
			when 0 then 0
			else cast(sum(si.used) as real)/cast(sum(si.reserved) as real)*100
		end as UsedPercent
		from sysobjects as so join sysindexes as si on (so.id=si.id)
		where so.type='U' and so.name<>'dtproperties'
		group by so.name) as t2 on (t1.TableName=t2.TableName)
--/*
open crsr1
fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent
while @@fetch_status>=0 
begin
	select @Description=cast(value as nvarchar(300))
		from ::fn_listextendedproperty('MS_Description','user','dbo','table',@TableName,null,null)
	if @Description is null or @Description='' or @@rowcount=0
		select @Description='null'
	else
		select @Description=quotename(@Description, '''')
	select @Category=cast(value as nvarchar(300))
		from ::fn_listextendedproperty('Category','user','dbo','table',@TableName,null,null)
	if @Category is null or @Category='' or @@rowcount=0
		select @Category='null'
	else
		select @Category=quotename(@Category, '''')
	select @SQL=N'insert into #tbl1 '
		+'select '
		+quotename(@TableName,'''')
		+', '+cast(@Columns as nvarchar(50))
		+', '+cast(@Rows as nvarchar(50))
		+', '+cast(@ReservedKB as nvarchar(50))
		+', '+cast(@UsedKB as nvarchar(50))
		+', '+cast(@UsedPercent as nvarchar(50))
		+', '+@Category
		+', '+@Description
-- print @SQL
	execute(@SQL)
	fetch next from crsr1 into @TableName, @Columns, @Rows, @ReservedKB, @UsedKB, @UsedPercent
end
close crsr1
deallocate crsr1
select * from #tbl1 order by TableName
drop table #tbl1
--*/
GO

This SQL code provides additional info about the tables on a SQL 2005 server:

select * from sys.tables

Get info about columns on a server

The system stored procedure of sp_columns table returns info about the columns in a table of SQL Server.

--Next: Columns in all the tables
sp_columns @table_name=N'%'
--Next: Columns in specified table
sp_columns tblX

EG: To return all info about all columns that have "ID" as part of its name, try this: sp_columns @table_name=N'%', @column_name=N'%ID%' .

This SQL code gets info about each column in each table in that database.

select t.name as TableName,
	c.name as ColumnName,
	dt.name as DataType,
	c.length, c.prec as "precision", c.scale, c.isnullable
from sysobjects as t
	join syscolumns as c on (t.id = c.id)
	join systypes as dt on (c.xtype = dt.xtype)
where t.type ='U'
	and t.name not like 'dtproperties'
	and t.name not like '%tmp%'
	and dt.name not like '%sys%'
order by t.name, c.name

However if your database happens to have user defined datatypes, then try this code suggested by Vadivel Mohanakrishnan of Vadivel.blogspot.com.

Select 
    so.name "Table Name", 
    sc.name "Field Name", 
    type_name (sc.xusertype) "Data Type", 
    sc.length "Size",
    "Is Nullable" =
        Case
            When sc.isnullable = 1 then 'Null' 
            Else 'Not Null'
        End
From 
    syscolumns sc, 
    sysobjects so 
Where 
    so.type = 'U' and 
    sc.id = so.id and 
    so.status > 0

This SQL code provides additional info about the columns in a SQL 2005 server:

select * from sys.columns

Get info about indexes in a database

This SQL code gets info about all the indexes in a database.

select t.name as TableName, i.name as IndexName,
	case i.indid when 1 then 1 else 0 end as "IndexClustered",
	i.keycnt, i.rows
from sysindexes as i join sysobjects as t on i.id = t.id
where t.type ='U'
	and t.name not in('dtproperties', 'SomeTableYouDontWant', 'test')
	and i.rows > 0
	and i.keycnt > 0
order by t.name, i.name

This SQL code gets the name of the columns used by all the indexes in a database.

select distinct
	t.name as TableName, i.name as IndexName
	, i.keycnt, ik.keyno, c.name
from sysobjects as t
	join sysindexes as i on t.id = i.id
	join sysindexkeys as ik on i.indid = ik.indid
	join syscolumns as c on ik.colid = c.colid
where t.type ='U'
	and t.name not in('dtproperties', 'SomeTableYouDontWant', 'test')
	and i.rows > 0
	and i.keycnt > 0
	and t.id = ik.id
	and t.id = c.id
order by t.name, i.name, ik.keyno

Using @@Identity to get ID just made

This SQL code inserts a row into a table and returns the ID that was automatically generated.

CREATE PROCEDURE insAppointment
	@Category varchar(50) = NULL, 
	@ScheduledDate datetime = NULL,
	@ScheduledTime datetime = NULL,
	@DrID varchar(9),
	@Note varchar(500) = NULL
AS
SET NOCOUNT ON
INSERT INTO Appointment(Category, ScheduledFor, DrID_FK, Note) 
VALUES(@Category, @ScheduledDate+@ScheduledTime, @DrID_FK, @Note)
SELECT @@IDENTITY AS 'Identity'
SET NOCOUNT OFF
GO

Here is some VBScript code that puts the ID generated into a variable.

sql= _
    "spCCSI_insAppointment " & _ 
        "@Category='" & strAppointmentCategory & "', " & _
        "@ScheduledDate='" & strScheduledDate & "', " & _
        "@ScheduledTime='" & strScheduledTime & "', " & _
        "@Physician='" & strPhysician & "', " & _
        "@Note='" & strNote & "'"
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open sql, cnn
strAppointmentID = strPrep(rst(0))

In MySQL, it's much simpler.

insert into t2 values(null,79); -- insert null for auto_increment columns
select last_insert_id();        -- to get latest ID 
select @@identity;              -- to get latest ID as of MySQL 3.23.25
-- For multiple row inserts, last_insert_id() returns the 1st generated ID

Semi-related to this topic is inserting non-auto generated values into the ID column.

In SQL Server, you have to set identity_insert first. Use this to enable you to insert values of your choice into an identity column (a field that automatically generates the ID). Only one table per session can have this on at a time so make sure you turn it off as soon as possible.

SET IDENTITY_INSERT t1 ON;
insert into t1 (99, 'test');
SET IDENTITY_INSERT t1 OFF;

In MySQL, you just insert your value into the ID column as you would normally.

insert into t1 (99, 'test');

Select top and sum the rest

This SQL code gets the top twelve and sums and counts the rest. Fancy code is needed because UNION does not work well with multiple instances of ORDER BY.

This way uses a temporary table:

if object_id('tempdb..#temp') is not null 
    drop table #temp
create table #temp (ProcedureID int, CPT nvarchar(308), Charges money)

--This is the top 12
insert into #temp (ProcedureID, CPT, Charges)
select top 12 
Fsvc.ProcedureID as ProcedureID, 
[Procedure] as CPT, 
sum(ChargeAmount) as Charges
from fact_Service as Fsvc
join dimension_Procedure as Dpro
on (Fsvc.ProcedureID = Dpro.ProcedureID)
group by [Procedure], Fsvc.ProcedureID
order by sum(ChargeAmount) desc

--This is the rest
insert into #temp (ProcedureID,CPT,Charges)
select 
cast(0 as int) as ProcedureID, 
cast(
    cast(
        (select (count(distinct fact_Service.ProcedureID)-12) from fact_Service)
        as nvarchar(308)
        )+
    ' Other CPTs'
    as nvarchar(308)) as CPT, 
sum(Fsvc1.ChargeAmount) as Charges
from fact_Service as Fsvc1
where Fsvc1.ProcedureID not in (
	select top 12 Fsvc2.ProcedureID
	from fact_Service as Fsvc2
	join dimension_Procedure as Dpro
	on (Fsvc2.ProcedureID = Dpro.ProcedureID)
	group by Fsvc2.ProcedureID
	order by sum(ChargeAmount) desc
)
select * from #temp

This way uses a variable of type TABLE:

declare @myTable table(ProcedureID int, CPT nvarchar(308), Charges money)

insert into @myTable (ProcedureID, CPT, Charges)
select top 12 f.ProcedureID as ProcedureID, [Procedure] as CPT, sum(ChargeAmount) as Charges
from fact_Service as f join dimension_Procedure as d on (f.ProcedureID = d.ProcedureID)
group by [Procedure], f.ProcedureID
order by sum(ChargeAmount) desc
--select * from @myTable

insert into @myTable (ProcedureID, CPT, Charges)
select 
	cast(0 as int) as ProcedureID, 
	cast(
		cast(
			(select (count(distinct fact_Service.ProcedureID)-12) from fact_Service)
			as nvarchar(308)
			)+
		' Other CPTs'
		as nvarchar(308)) as CPT, 
	sum(f1.ChargeAmount) as Charges
from fact_Service as f1
where f1.ProcedureID not in (
	select top 12 f2.ProcedureID
	from fact_Service as f2
	join dimension_Procedure as d1
	on (f2.ProcedureID = d1.ProcedureID)
	group by f2.ProcedureID
	order by sum(ChargeAmount) desc
)
select * from @myTable

Using remote servers

In Microsoft SQL Server, to use a remote server you have to take two preliminary steps.

First, add a "Linked Server" to the master database on the local server. This is done using a specific system stored procedure. EG:

--This is for a server on the network
exec master.dbo.sp_addlinkedserver
@server=N'RemoteServerName',
@srvproduct=N'SQL Server'

--This is for a server on the Internet
exec master.dbo.sp_addlinkedserver
@server=N'AliasForRemoteServer', 
@srvproduct=N'', 
@datasrc=N'SQLServerName.SiteName.com', 
@provider='SQLNCLI'

Second, provide a method for authenticating. EGs:

--This uses the user's credentials
exec master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'RemoteServerName'
--This is useful if you are on server A and using linked server B.

--This sets up credentialing ahead of time
exec master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'RemoteServerName',
@locallogin=NULL ,
@useself=N'False',
@rmtuser=N'SQLLogin',
@rmtpassword=N'SQLPassword'
--This is useful if you are on server C, connecting to server A,
--which in turn is using linked server B. [support.microsoft.com/...]

After that, it's simply a matter of fully qualifying objects on the linked server. EG:

select * from RemoteServerName.remoteDatabase.dbo.remoteTable

You can remove access to a remote server too. EGs:

--This removes default logins
exec sp_droplinkedsrvlogin
@rmtsrvname=N'RemoteServerName'

--This removes specific logins
exec sp_droplinkedsrvlogin
@rmtsrvname=N'RemoteServerName',
@locallogin=NULL

--This removes the linked server and login
exec sp_dropserver 
@server='RemoteServerName', 
@droplogins='droplogins'

These few lines help in getting your orientation when dealing with multiple servers:

select @@servername, @@version;
select * from master.dbo.sysservers; -- MSSS 2000/v8+
select * from master.sys.servers;    -- MSSS 2005/v9+

Search and replace within fields

This beautiful bit of SQL can save a lot of time. It's from Search & Replace in MySQL tables!! [blog.igeek.info/still-fresh/2005/08/31/search-replace-in-mysql-tables/]

update table1 set field1 = replace(field1, "old string", "new String")

Note that the above searches and replaces within each field. In contrast the following searches and replaces entire fields.

update table1 set field1 = "new String" where field1 = "old string"

Find a column or table in the database

Given a column name or part of a column name, this returns a list of table and column names where the column name matches the given column name.

select TableName=t.name, ColumnName=c.name from sysobjects t join syscolumns c on (t.id=c.id)
where c.id in (select id from syscolumns where name like '%org%') and c.name like '%org%' and t.type='U'

Given a table name or part of a table name, this returns a list of matching table names.

select name from sysobjects where name like '%dress%' and type='U'

Find duplicates in tables

Sometimes duplicates are OK (EG: The User_FirstName column may have many duplicates), but sometimes duplicates are not OK (EG: The User_Login column should have no duplicates). It is best to programmatically prevent unwanted duplicates from entering the system in the first place but sometimes you have to deal with what you're given (EG: You might be have to merge a list of doctors from two meetings).

Here is some simple SQL to check for duplicates:

SELECT User_Login, COUNT(User_Login) AS Occurrences
FROM User
GROUP BY User_Login
HAVING ( COUNT(User_Login) > 1 )

If the table has a multi-column identifier, use code like this:

SELECT FirstName, LastName, COUNT(*) AS Occurrences
FROM Doctor
GROUP BY FirstName, LastName
HAVING ( COUNT(*) > 1 )

One simple way to get rid of true duplicates:

select distinct * into #holding from patient;
truncate table patient;
insert patient select * from #holding;
drop table #holding;

Split column into first and last names

This assumes that the column to be split has data in this format: "Last Name, First Name" and that there is only one comma.

select
LastName  = left(LastFirstName,charindex(', ',LastFirstName)-1),
FirstName = substring(LastFirstName,charindex(', ',LastFirstName)+2,len(LastFirstName))

It is much easier to do this sort of thing in Excel with Data > Text to Columns.

Get all values for a column as a list

The limitation of this is the max size of varchar.

declare @array varchar(8000)
select @array = coalesce(@array+',', '') + SomeColumn from SomeTable
select @array

Select random row from a table

This assumes that the table has primary key ID integer column.

DECLARE @max int, @min int
SELECT @max = MAX(MyTableID) FROM MyTable
SELECT @min = MIN(MyTableID) FROM MyTable
SELECT MyTableID FROM MyTable WHERE MyTableID = ROUND(@min + (RAND() * (@max-@min)),0)

This select several fairly random rows:

SELECT TOP 10 * FROM YourTable ORDER By NEWID()

Pad a value

It is common to pad an integer with zeroes or to pad a string with spaces on the left or right side. The latter is frequently used on older column counted data.

--Pad an integer with zeroes. In this case: Make a 4 digit PIN.
select right(replicate('0',4) + cast(78 as varchar(4)), 4)
select right('0000'  + convert(varchar(4), 78), 4) --same thing

--Pad a string with spaces. In this case: Make a 16 character string.
select right(space(16) + 'foo bar', 16)

Backup and restore

Backup frequently. Restore to recover a database or to copy a database. This sort of backup saves all the data, functions, stored procedures, views, etc.

backup database dbX to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\dbX_200610251613.bak'

restore database dbX 
    from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup\dbX_200610251613.bak'
    with move 'dbX_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\dbX.mdf',
    move 'dbX_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data\dbX.ldf'

Swap the value of 2 columns

If you've put the values of one column in the other and vice versa, this bit of T-SQL can undo it.

declare @myTemp varchar(63);
update employee set @myTemp=FirstName, FirstName=LastName, LastName=@myTemp;

Insert into an Identity column

Identity columns autmoatically generate values when you do an insert but sometimes you want to insert a specific value. The trick is to turn IDENTITY_INSERT on for the table and then do the insert.

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable (ID, SomeColumn)
VALUES (99, 'Row with Identity column specified')

SET IDENTITY_INSERT IdentityTable OFF

A few pointers:

Don't forget to turn it off since it can on for only one table at a time.
If it's on then you must provide a value for the Identity column.
If the Identity value you insert is greater than the current seed, then it becomes the new seed.

Find rows in one table but not in another

For a single-column ID:

select *
from a
where not exists (select * from b where b.ID=a.ID)

For a multi-column ID:

select *
from a
where not exists (select * from b where b.ID=a.ID and b.foo=a.foo and b.bar=a.bar)

Page Modified: (Hand noted: 2008-08-20 14:40:44Z) (Auto noted: 2008-08-19 20:28:52Z)