Query to find number Rows, Columns, ByteSize for each table in the current database

USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Changing Database Collation

A great article on changing a SQL 2005′s database collation.

http://www.db-staff.com/index.php/microsoft-sql-server/69-change-collation

Linked Select Boxes

Here’s a sample of some javascript I recently developed for linked select boxes. Enjoy.

<p> </p> <form name="form1">
<select onchange="updateSelect(this.options[this.selectedIndex].value,document.form1.select2);" size="1" name="select1">
<option selected="" value="">-- Select --</option>
<option value="1|apple*2|pear*3|orange">Fruit</option>
<option value="4|carrot*5|tomato*6|potato*7|cauliflower">Vegetable</option> <option value="8|fish*9|lamb*10|cow">Meat</option>
</select>
<select size="1" name="select2">
<option selected="" value="">-- Select --</option>
</select>
</form>
<p>
<script type="text/javascript">
function updateSelect(selectedValue,selectTarget){
selectTarget.options.length = 0
if (selectedValue!='') {
selectTarget.options[selectTarget.options.length]=new Option('-- Select --','')
sTempAry = selectedValue.split("*")
for (i=0; i<sTempAry.length; i++) {
sTempValAry = sTempAry[i].split("|");
selectTarget.options[selectTarget.options.length]=new Option(sTempValAry[1],sTempValAry[0]) }
}
else {selectTarget.options[selectTarget.options.length]=new Option('-- Select --','') }
}
</script></p>

Remove duplicate rows from a table

http://support.microsoft.com/kb/139444

Truncating Log Files and Recovering Space

For SQL 2005…

http://www.sqlcleanup.com/2008/sql-2005-truncating-log-files-and-recovering-space/

Here’s an updated technique for SQL 2008…
Use <database Name>
GO

Alter Database <database Name> Set Recovery Simple
GO

Alter Database <database Name> Set Recovery Full
GO

DBCC SHRINKFILE (‘<log file name>’, 1)
GO

Restoring .bak File to Another Database or Different Server

This is always a bit tricky…

Here’s the procedure step by step.

Adding a column to a Replicated Table

Adding a column to a table being replicated can be a pain. Here’s a better way…

exec sp_repladdcolumn '[dbo].[table_name]','column_name',[varchar(200)], '%', '1'

to delete...
exec sp_repldropcolumn '[dbo].[survey]','sr_english_sur_id'

Just rebuild your snapshots and you're all set.

List All Exchange Email Addresses

Here’s a real time saver for you Exchange admins out there that need a quick list of all the email account on the domain…

This script I picked up and modified from the ExchangepediaBlog does the trick!

<p><font face="Courier New">'==================================================================================================
' VBScript Source File
' NAME: LISTEMAIL.VBS
' VERSION: 1.0
' BY: Dave Abad at Logical Alternative
'     Modified From Bharat Suneja's ExchangePediaBlog Post LISTPROXYADDRESSES.VBS
'==================================================================================================
'Set up constant for deleting values from multivalued attribute memberOf
Const ADS_PROPERTY_NOT_FOUND  = &h8000500D
Const ADS_UF_ACCOUNTDISABLE = 2 'For UserAccountControl
Const strX400Search = "X400"
'______________________________________________________

'Set RootDSE
Set objRootDSE = GetObject("LDAP://rootDSE")
strDomain = objRootDSE.Get("defaultNamingContext")
strADPath = "LDAP://" & strDomain
'wscript.Echo strADPath
Set objDomain = GetObject(strADPath)
'wscript.echo "objDomain: " & objDomain.distinguishedName

'Setup ADODB connection
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=ADsDSOObject;"
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection

'Execute search command to look for Contacts & Groups
sCT = "<" & strADPath & ">;(&(|(objectClass=contact)(objectClass=group))(mail=*))"
sCT = sCT & ";distinguishedName,displayName,mail,proxyAddresses;subtree"
objCommand.CommandText = sCT

'Execute search to get Recordset
Set objRecordSet = objCommand.Execute

'Start procedure
strResult = strResult & VbCrLf & "Domain: " & strDomain
strResult = strResult & VbCrlf &  "#Total Records Found (other accounts): "
strResult = strResult & objRecordSet.RecordCount & VbCrlf
AddressCount = 0

While Not objRecordSet.EOF 'Iterate through the search results
 'Get User's distinguished name from Recordset into a string
 strUserDN = objRecordSet.Fields("distinguishedName")
 'Use string to bind to user object
 set objUser= GetObject("LDAP://"& strUserDN & "")
 strResult = strResult & objUser.mail & VbCrLf
 objRecordSet.MoveNext
Wend

'*************************************
'Begin second query for users
varDisabledCounter = 0                

'Execute search command to look for user
sCT = "<" & strADPath & ">;(&(objectClass=user)(mail=*))"
sCT = sCT & ";distinguishedName,displayName,mail,proxyAddresses;subtree"
objCommand.CommandText =  sCT

'Execute search to get Recordset
Set objRecordSet = objCommand.Execute

strResult = strResult & vbCrlf &  "#Users"
strResult = strResult & VbCrlf &  "#Total Records Found (users): "
strResult = strResult & objRecordSet.RecordCount & VbCrlf

While Not objRecordSet.EOF 'Iterate through the search results
 'Get User's distinguished name from Recordset into a string
 strUserDN = objRecordSet.Fields("distinguishedName")
 'Use string to bind to user object
 set objUser= GetObject("LDAP://"& strUserDN & "")
 If objUser.AccountDisabled = TRUE Then
 varDisabledCounter = varDisabledCounter + 1
 strResult2 = strResult2 & objUser.mail & VbCrLf
 Else
 strResult = strResult & objUser.mail & VbCrLf
 End If
 objRecordSet.MoveNext
Wend

strOut = "Users, Groups & Contacts" & VbCrLf
strOut = strOut & "-------------------------" & VbCrLf & strResult
strOut = strOut & VbCrLf & "Disabled Users"
strOut = strOut & VbCrLf & "-------------------------" & VbCrLf & strResult2
'WScript.Echo strResult

'Output to a text file
aDomain = split(strDomain,"=")
aDomain2 = split(aDomain(1),",")
strDomain = aDomain2(0)
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFileSystem.CreateTextFile(".\" & strDomain & "_" & "email_list.txt")
objOutputFile.Write strOut</font></p>

Text Fade DHTML Marquee

Wow, our first recursive tip and by request no less.
Here’s source for the client testimonial effect on the the home page.

<script type="text/javascript">
var delay = 4000; //set delay between message change (in milliseconds)
var maxsteps=30; // number of steps to take to change from start color to endcolor
var stepdelay=40; // time in milliseconds of a single step
//**Note: maxsteps*stepdelay will be total time in milliseconds of fading effect
var startcolor= new Array(255,255,255); // start color (red, green, blue)
var endcolor=new Array(0,0,0); // end color (red, green, blue)

var fcontent=new Array();
begintag='<div style="padding: 10px;">';
fcontent[0]="Thank you for all your help along the way. I have gotten amazingly good feedback from both the Open House (nearly 200 people came!) and your marketing material. Thank you once again for that excellent work! The website was so wonderful and I can't believe you and your team were able to create it so quickly. Totally fabulous!<br><br> Dr. Molly Force, Owner and Director Prosper Natural Health. Port Townsend, WA.";
fcontent[1]="David first began working for us as our IT specialist. However, since that time, we have used David and his team on a variety of projects including website construction and database development and management. David has always exceeded all expectations on every project.<br><br> John McQueen, Principal Anderson-McQueen Funeral and Cremation Centers. St. Petersburg, FL.";
fcontent[2]="David has been a godsend to our company. He is both knowledgeable and creative in the ways in which he addresses business problems. He also operates with the utmost of integrity in his dealings with clients. I highly recommend him!<br><br> Heather Ceresoli, Division Director Taylor White Consulting. St. Petersburg, FL.";
fcontent[3]="David\'s firm has provided exceptional service to our firm as we have grown through the years. We highly recommend his organization.<br><br> Jim Bassil, Partner Taylor White Consulting. St. Petersburg, FL."
fcontent[4]="Wow. I take off my hat. Thank you for your dedication in working around the clock to resolve the issue.<br><br> Shoji Masuzawa, Director of IT Center for Social and Emotional Education. NY, NY."
fcontent[5]="Steven, Thank you for being there this morning. It means a lot to me that you would take the extra time to be there to assist our vendors with the diagnosis of our server hardware issues.<br><br> Jay Smithweck, Owner Autoexact.com. Tampa, FL."
closetag='</div>';

var fwidth='200px'; //set scroller width
var fheight='240px'; //set scroller height

var fadelinks=1; //should links inside scroller content also fade like text? 0 for no, 1 for yes.

///No need to edit below this line/////////////////

var ie4=document.all&&!document.getElementById;
var DOM2=document.getElementById;
var faderdelay=0;
var index=0;

/*Rafael Raposo edited function*/
//function to change content
function changecontent(){
if (index>=fcontent.length)
index=0
if (DOM2){
document.getElementById("fscroller").style.color="rgb("+startcolor[0]+", "+startcolor[1]+", "+startcolor[2]+")"
document.getElementById("fscroller").innerHTML=begintag+fcontent[index]+closetag
if (fadelinks)
linkcolorchange(1);
colorfade(1, 15);
}
else if (ie4)
document.all.fscroller.innerHTML=begintag+fcontent[index]+closetag;
index++
}

// colorfade() partially by Marcio Galli for Netscape Communications. ////////////
// Modified by Dynamicdrive.com

function linkcolorchange(step){
var obj=document.getElementById("fscroller").getElementsByTagName("A");
if (obj.length>0){
for (i=0;i<obj.length;i++)
obj[i].style.color=getstepcolor(step);
}
}

/*Rafael Raposo edited function*/
var fadecounter;
function colorfade(step) {
if(step<=maxsteps) {
document.getElementById("fscroller").style.color=getstepcolor(step);
if (fadelinks)
linkcolorchange(step);
step++;
fadecounter=setTimeout("colorfade("+step+")",stepdelay);
}else{
clearTimeout(fadecounter);
document.getElementById("fscroller").style.color="rgb("+endcolor[0]+", "+endcolor[1]+", "+endcolor[2]+")";
setTimeout("changecontent()", delay);

}
}

/*Rafael Raposo's new function*/
function getstepcolor(step) {
var diff
var newcolor=new Array(3);
for(var i=0;i<3;i++) {
diff = (startcolor[i]-endcolor[i]);
if(diff > 0) {
newcolor[i] = startcolor[i]-(Math.round((diff/maxsteps))*step);
} else {
newcolor[i] = startcolor[i]+(Math.round((Math.abs(diff)/maxsteps))*step);
}
}
return ("rgb(" + newcolor[0] + ", " + newcolor[1] + ", " + newcolor[2] + ")");
}

if (ie4||DOM2)
document.write('<div id="fscroller" style="border:1px solid black;width:'+fwidth+';height:'+fheight+'"></div>');

if (window.addEventListener)
window.addEventListener("load", changecontent, false)
else if (window.attachEvent)
window.attachEvent("onload", changecontent)
else if (document.getElementById)
window.onload=changecontent
</script>

Parsing CSV Files

I use split quite a bit for string manipulation and ran into an interesting issue the other day with it when parsing a CSV file with a multi-value field that itself contained comma delimited data.

For example….

214,Bob,Johnson,12/28/1968,(Florida,Texas,Washington),$3,444,234.03

RegEx to the rescue….

Dim re
 Set re = new RegExp
 re.Global = true
 re.pattern = ",(?=[^()]*\))"
 re.Replace(strInput, "|")