Individual - Washington, DC

T-SQL

Search all columns in all tables for an occurrence of a string

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + 'CHANGE_THIS_TO_SEARCH_CRITERIA' + '%','''')

WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

Replace text in every column in every table in a database. This was developed to recover from the recent SQL Injection hacks that have been coming out of China. You only need to change the @badtext variable on line 2 (@replacewith too if necessary) then run this in Query Analyzer.

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @BadTextFixed nvarchar(110), @SQL nvarchar(4000), @ReplaceCount int, @BadText nvarchar(120), @ReplaceWith nvarchar(120) SET @BadText = 'Bad text goes here' SET @ReplaceWith = '' SET @TableName = '' SET @BadTextFixed = QUOTENAME('%' + @BadText + '%','''') SET @ReplaceCount = 0 WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 ) WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN SET @SQL= 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ' + QUOTENAME(@BadText, '''') + ', ' + QUOTENAME(@ReplaceWith, '''') + ') WHERE ' + @ColumnName + ' LIKE ' + @BadTextFixed EXEC (@SQL) SET @ReplaceCount = @ReplaceCount + @@ROWCOUNT END END END SELECT CAST(@ReplaceCount AS varchar) + ' instances replaced' AS 'Results'

Retreive everything in a string based column up to the first comma.

SUBSTRING(dbo.mytable.keywords, 1, CAST(PATINDEX('%,%', replace(dbo.mytable.keywords, ' ', ',')) -1 as integer))

Select by specific date, ignoring the time portion

SELECT count(dt) as hits, CONVERT(CHAR(10),dt,120) as date FROM visitors

group by CONVERT(CHAR(10),dt,120)

order by CONVERT(CHAR(10),dt,120) desc

Dynamic SQL example

CREATE PROCEDURE dbo.Emailer

@storeid integer

exec ('Insert into #temp (CustID, CustEmail, CustFirstName, CustLastName)

SELECT CustID, ltrim(rtrim(CustEmail)), CustFirstName, CustLastName

FROM dbo.Customer C

WHERE (CustID IN

(SELECT CustID

FROM SnapshotOrders

WHERE StID = ' + @storeid + '))')

Remove duplicate records from a table

DELETE FROM Items

WHERE EXISTS (

SELECT Id

FROM Items ItemsInner

WHERE ItemsInner.Itemnumber = Items.Itemnumber

AND ItemsInner.Id < Items.Id )

Remove duplicates in a temp table

(We've used this for a client to remove duplicates prior to sending a mass email)

CREATE PROCEDURE dbo.EmailerMComposeRun AS

declare @sql nvarchar (2000)

declare @fixsql nvarchar (2000)

declare @stid integer

create table #temp (CustID integer, CustEmail varchar(100), CustFirstname varchar (70), CustLastName varchar(80))

set @sql = 'Insert into #temp (CustID, CustEmail, CustFirstName, CustLastName) SELECT CustID, ltrim(rtrim(CustEmail)), CustFirstName, CustLastName

FROM dbo.Customer C

WHERE (CustID IN

(SELECT CustID

FROM tblOrders

WHERE StID = 1)) AND (CustEmail IS NOT NULL) AND CustEmail LIKE ''%@%'' AND CustEmail LIKE ''%.%'''

exec sp_executesql @sql

set @fixsql = '

DELETE FROM #temp

WHERE EXISTS (

SELECT CustID

FROM #temp GetCustInner

WHERE GetCustInner.CustEmail = #temp.CustEmail

AND GetCustInner.CustID < #temp.CustID )'

exec sp_executesql @fixsql

Select * from #temp

drop table #temp

Get the ID of the record added in a stored procedure

CREATE PROCEDURE dbo.CreateGallery

@galleryname varchar(80),

@galleryid int OUTPUT

insert into photogallery(galleryname)

VALUES (@galleryname)

SET @GalleryID = @@IDENTITY

Get a list of all records added in the last X days

SELECT COUNT(*) as ItemsAdded, CONVERT(varchar(8), dateadded, 101) as DateAdded

FROM dbo.products

where dateadded > DATEADD(Day, -10, getdate())

group by CONVERT(varchar(8), dateadded, 101)

order by CONVERT(varchar(8), dateadded, 101) desc

Replace a string of data in a TEXT field.

UPDATE TableName SET myFieldName = REPLACE(SUBSTRING(myFieldName, 1, DATALENGTH(myFieldName)), 'My Bad Text', 'My New Text')

Get recordcounts for all tables in a database.

select 'Table Name'=convert(varchar(50),t.TABLE_NAME), 'Records'=max(i.rows) from sysindexes i, INFORMATION_SCHEMA.TABLES t where t.TABLE_NAME = object_name(i.id) and t.TABLE_TYPE = 'BASE TABLE' group by t.TABLE_SCHEMA, t.TABLE_NAME ORDER BY Records DESC

Update one table with information from another

UPDATE dbo.archive

SET dbo.archive.name = dbo.clients.name

FROM clients

WHERE clients.clientID = archive.clientID

Update one table (accessoryitems) with information from another (products)

INSERT INTO AccessoryItems(productid, accessoryid)

SELECT products.productID, products.accessories as Accessoryid

FROM Products

Pad a varchar field with some character (zeros in this case)

UPDATE Tracking

SET trackingnumber = Right('0000000000'+CAST(trackingnumber AS varchar(10)),10)

Very simple case statement

CASE WHEN left(Name, 3) = 'old' THEN (dbo.clientinfo.Last_Name) ELSE (dbo.clientarchive.LastName) END

Pick a random record from a table (the NEWID() function is the key here)

SELECT TOP 1 dbo.products.ProductName, dbo.products.ProductImage, dbo.products.ProductID

FROM dbo.products

ORDER BY NEWID()

Combine multiple columns or rows into one string variable or comma delimited value

(The output here would be CardNumbers = "02/2007, 02/2007, 02/2007")

DECLARE @AList varchar(25)

SELECT @AList = COALESCE(@AList + ', ', '') + cast(cardexpmonth as varchar(2)) + '/' + cast(cardexpyear as varchar(4))

FROM dbo.clientinfo

SELECT @AList as CardNumbers

Coalesce means 'Take this column and if its not null or the first record, add the comma and then add whatever other columns I want and then keep looping. I use this to dynamically populate keywords on a data generated page.

COLD FUSION

Round down to the nearest tenths place

There are no functions that allow this since all of them round up if a decimal is higher than .5. The following will "fake" a round down by dropping the last digit. You can round to the nearest hundredths by just changing the numberformat mask in the function. Explaination: Force a number to the hundredths, reverse it, remove the first (formerly last) digit, reverse it again, add the last zero back.

#numberformat(reverse(removechars(reverse(numberformat(thenumber, "999999.99")), 1, 1)), "999999.99")#

Always round up to the nearest tenths place

Same idea as the above example but this forces a round up when you have an integer with 3 floated numbers (thousanths place). Explaination: If the number has 2 decimal places, leave it. Otherwise add .004 if it wouldn't round up on it's own (.5 or greater).

Simple pagination routine

<cfquery NAME="Getnames" DATASOURCE="icard_trans"> SELECT Name FROM Clients ORDER BY Name </cfquery> <cfparam name="url.startRow" default="1"> <cfparam name="url.rowsPerPage" default="20"> <cfif not isNumeric(url.startRow) or url.startRow lt 1 or url.startRow gt GetTrans.recordCount or round(url.startRow) neq url.startRow> <cfset url.startRow = 1> </cfif> <cfset totalRecords = Getnames.recordcount> <cfset totalPages = totalRecords / rowsPerPage> <cfset endRow = (startRow + rowsPerPage) - 1> <cfparam name="currentPage" default="1">  <cfif endRow GT totalRecords> <cfset endRow = totalRecords> </cfif>  <cfif (totalRecords MOD rowsPerPage) GT 0> <cfset totalPages = totalPages + 1> </cfif>  <cfif totalPages gte 2><strong>Pages: </strong> <cfloop from="1" to="#totalPages#" index="i"> <cfset startRow = ((i - 1) * rowsPerPage) + 1> <cfif currentPage neq i> <a href="search.cfm?startRow=#startRow#¤tPage=#i#"> <cfelse> <span class="pagination"> </cfif> #i# <cfif currentPage neq i> </a> <cfelse> </span> </cfif> </cfloop> </cfif> <cfoutput QUERY="Getnames" startrow="#url.startRow#" maxrows="#rowsPerPage#"> Your record output here </cfoutput>

Loop over all fields posted by a form

#i#: #evaluate(string)#

</cfloop>

Count occurances of some text within a string

This example searches for the number of line breaks in a variable called address (to see how many lines there are in an address)

Check to see if a user is already logged in on another computer or browser

This is probably the easiest way to do this. You just drop this on your login page. Change email to memberid or whatever you use as your identifier. Form/variable validation removed for this example.

</cfif>

</cfloop>

</cfif>

Alter columns without SQL Enterprise Manager or SQL Express.

This is a quick CF script to let you add columns to a table. Easy alteration will let you delete columns, etc.

Alter Table #form.tablename#

Add #columnname# #definition#

<cfif len(defaultvalue) GT 0>Default #defaultvalue#</cfif>

</cfquery>

select *

from #form.tablename#

</cfquery>

<tr>

</cfloop>

</cfoutput>

</table>

</CFIF>

select table_name

from information_schema.tables

</cfquery>

Alter Table<br>

<option value="#table_name#">#table_name#</option>

</cfoutput>

</select>

ADD

<option value="varchar(200)" SELECTED>varchar(200)</option>

<option value="smalldatetime">smalldatetime</option>

<option value="decimal(9,2)">decimal(9,2)</option>

<option value="number(8)">number(8)</option>

</select>

Default <input name="defaultvalue" type="text">;<br><br>

</form>

Get just the name of the current script with no path names or info.

#right(cgi.script_name, find('/', reverse(cgi.script_name), 1) - 1)#

Get everything up to the first space.

This is good for extracting just a first name from a full name. It can be modified to search for everything after the first comma or whatever you want, just change the " " to ",".

#mid(productname, 1, find(' ', productname, 1))#

Simple (free) CF/JS CAPTCHA style form validation.

This is a session based CAPTCHA code to help reduce spam submitted by web forms. It writes a javascript ascii code which is difficult for robots to read and outputs it on the page then writes a CF session variable which gets passed to the form validator.

Form Page:

<Table>

<tr><Td class="bodycopy" align="right">To reduce spam, please type the following number:

<cfoutput>document.write(String.fromCharCode(#thisascii#));</cfoutput>

</cfloop>

</script>

</td><td>

</td></tr>

</Table>

Validation Page:

</cfif>

Add a space before all uppercase letters

#REReplace("DiversifiedProgrammingEnterprises", "([A-Z])", " \1", "ALL")#

Strip all special characters

rereplace("#yourtext#", "[[:punct:]|[:space:]]", "", "All")

Export all columns and records in all tables in a database to Excel

Useful for backing up a table direct to a user through an admin area. In case of major failure, its a bit of work to restore since there are no separate worksheets but better than a major loss and makes the client feel better about having control over the data.

select table_name

from information_schema.tables

</cfquery>

#table_name#<br>

select *

from #table_Name#

</cfquery>

</font>

</td>

</cfloop>

</tr>

<td>

<cfif len(Evaluate("#QueryName#.#Item#[#Vars#]")) LT 1> <cfelse>#Evaluate("#QueryName#.#Item#[#Vars#]")#</cfif>

</td>

</cfloop>

</tr>

</cfloop>

</table><br>

</cfoutput>

Grab RSS news feeds from Yahoo News. This gets the top three.

<tr>

#objRSS.rss.channel.item[i].title.xmltext#</a></td>

#objRSS.rss.channel.item[i].description.xmltext#</a></td>

</cfoutput>

</tr>

</cfloop>

</table>

Docking loose tabs in Homesite 5

In the View menu, open Files 1 Primary. At the bottom of the tab (where the files appear, NOT where directories appear) right click and choose Move Files 1 To - Left side. Do the same thing for File 2 Secondary. For the snippets tab (and maybe others), redocking requires the following registry alteration. Close Homesite, click Start - Run. Type regedit. In the registry editor, navigate to HKEY_CURRENT_USER\Software\Macromedia\HomeSite5\Docking\frmDockSnippets. Make your registry look like the default reg settings, which are below:

"Visible"="1"

"AlwaysOnTop"="1"

"Position"="0,0,197,694"

"Floating"="0"

"DockHost"="AllaireTabDockForm0"

"PageIndex"="3"

"DockTab"="0"

Get dynamically generated form input boxes on the processing page

(Example: Input boxes in a loop called name1, job1, name2, job2, etc.)

#evaluate("form.name" & id)#

#evaluate("form.job" & id)#

Basic stored procedure code

</cfstoredproc>

Delete all session variables

Delete one particular session variables

Use CFFile to upload an attachment from a form posting to a different directory.

(remember to use enctype="multipart/form-data" instead of the default enctype="application/x-www-form-urlencoded" in your form!

</cfif>

Write a form posting to a text file in your current directory

Read from a text file

<cfoutput>#specialtext#</cfoutput>

CFDUMP all variables

</cfloop>

Get absolute directory path up one level

I wouldn't try to understand this one... your head would explode, but it works. We reverse the directory, strip to the second slash, reverse again.

A handy, short date loop. Remove the - before prevyears or leave to show years past

M <select name="birth_month">

<option value="#themonth#">#themonth#</option>

</cfloop>

</select>

D <select name="birth_day">

<option value="#theday#">#theday#</option>

</cfloop>

</select>

Y <select name="birth_year">

<option value="#dateformat(theyear, "yyyy")#">#dateformat(theyear, "yyyy")#</option>

</cfloop>

</select>

JAVASCRIPT

Disable the submit button to prevent double processing

onclick="this.disabled=1; this.form.submit(); this.value='Processing Order. Please wait...';"

Open the print dialog box

<a href="javascript:void(0);" onclick="window.print();">CLICK HERE TO PRINT THIS PAGE</a>

Open a confirm alert box. If they click No, it aborts the submission.

onclick="if (confirm ('Are you sure?')) {return true} else {return false}"

GOOGLE

Generate a result set compatible with Froogle

SELECT DISTINCT TOP 100 PERCENT

(dbo.products.ProductName) AS title,

cast(cast(dbo.products.ProductPrice AS Decimal(10,2)) as varchar) AS price,

'http://www.yoursite.com/images/large/' + dbo.products.ProductImageLg AS image_link,

cast('http://www.yoursite.com/productdetails.cfm?ID=' + cast(dbo.Products.productid AS Varchar) AS Varchar) AS link,

cast(dbo.products.ProductID as varchar) AS id,

ltrim(rtrim(left(dbo.products.ProductDescriptionShort, 255))) AS description,

replace(ltrim(rtrim(dbo.products.ProductName)), ' ', ',') AS label

FROM dbo.products

WHERE ProductPrice > 0 AND len(ProductName) > 3

Standard META tags

htaccess redirect a directory link to a file (client advertises a directory, redirect to a file instead).

RewriteRule ^Classes/?$ availableclasses.cfm [L,NC]

Amazon CloudSearch working example in PHP

<?php

error_reporting(E_ALL);

ini_set('display_errors', '1');

$feed_url = 'http://search-***YOUR AWS DOMAIN HERE***-***YOUR AWS DOMAIN ID HERE***.us-east-1.cloudsearch.amazonaws.com/2011-02-01/search?q=bain&return-fields=resourcename%2Clast_modified%2C&results-type=xml';

$xml = simplexml_load_file($feed_url);

foreach($xml->hits->hit as $hit){

echo $hit->d[1] . '<br>';

echo $hit->d[0] . '<br>';

echo '<br>';

}

CATEGORIES

T-SQL

Search all columns in all tables for an occurrence of a string

Replace text in every column in every table in a database. This was developed to recover from the recent SQL Injection hacks that have been coming out of China. You only need to change the @badtext variable on line 2 (@replacewith too if necessary) then run this in Query Analyzer.

Retreive everything in a string based column up to the first comma.

Select by specific date, ignoring the time portion

Dynamic SQL example

Remove duplicate records from a table

Remove duplicates in a temp table

Get the ID of the record added in a stored procedure

Get a list of all records added in the last X days

Replace a string of data in a TEXT field.

Get recordcounts for all tables in a database.

Update one table with information from another

Update one table (accessoryitems) with information from another (products)

Pad a varchar field with some character (zeros in this case)

Very simple case statement

Pick a random record from a table (the NEWID() function is the key here)

Combine multiple columns or rows into one string variable or comma delimited value

COLD FUSION

Round down to the nearest tenths place

Always round up to the nearest tenths place

Simple pagination routine

Loop over all fields posted by a form

Count occurances of some text within a string

Check to see if a user is already logged in on another computer or browser

Alter columns without SQL Enterprise Manager or SQL Express.

Get just the name of the current script with no path names or info.

Get everything up to the first space.

Simple (free) CF/JS CAPTCHA style form validation.

Add a space before all uppercase letters

Strip all special characters

Export all columns and records in all tables in a database to Excel

Grab RSS news feeds from Yahoo News. This gets the top three.

Docking loose tabs in Homesite 5

Get dynamically generated form input boxes on the processing page

Basic stored procedure code

Delete all session variables

Delete one particular session variables

Use CFFile to upload an attachment from a form posting to a different directory.

Write a form posting to a text file in your current directory

Read from a text file

CFDUMP all variables

Get absolute directory path up one level

A handy, short date loop. Remove the - before prevyears or leave to show years past

JAVASCRIPT

Disable the submit button to prevent double processing

Open the print dialog box

Open a confirm alert box. If they click No, it aborts the submission.

GOOGLE

Generate a result set compatible with Froogle

Standard META tags

htaccess redirect a directory link to a file (client advertises a directory, redirect to a file instead).

Amazon CloudSearch working example in PHP