Developer's Closet A place where I can put my PHP, SQL, Perl, JavaScript, and VBScript code.

26Jul/100
Query Table Sizes from a SQL Database

I pulled apart a few stored procedures and ended up with this code. Very nice way to query the row count and size for each table. Take a look at the code, it is simpler than you might think. To use, change the database name at the top of the script to any database you wish to query. I turned this into a really handy stored procedure. When I call the stored procedure I pass the database name. Possibilities are endless.

use master
go

BEGIN try 
DECLARE @table_name VARCHAR(500) ; 
DECLARE @schema_name VARCHAR(500) ; 
DECLARE @tab1 TABLE(
        tablename VARCHAR (500) collate database_default
,       schemaname VARCHAR(500) collate database_default
); 
DECLARE  @temp_table TABLE (    
        tablename sysname
,       row_count INT
,       reserved VARCHAR(50) collate database_default
,       data VARCHAR(50) collate database_default
,       index_size VARCHAR(50) collate database_default
,       unused VARCHAR(50) collate database_default 
); 

INSERT INTO @tab1 
SELECT t1.name
,       t2.name 
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

DECLARE c1 CURSOR FOR 
SELECT t2.name + '.' + t1.name  
FROM sys.tables t1 
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );   

OPEN c1; 
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0 
BEGIN  
        SET @table_name = REPLACE(@table_name, '[',''); 
        SET @table_name = REPLACE(@table_name, ']',''); 

        -- make sure the object exists before calling sp_spacedused
        IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
        BEGIN
                INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
        END
        
        FETCH NEXT FROM c1 INTO @table_name; 
END; 
CLOSE c1; 
DEALLOCATE c1; 
SELECT t1.*
,       t2.schemaname 
FROM @temp_table t1 
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY  schemaname,tablename;
END try 
BEGIN catch 
SELECT -100 AS l1
,       ERROR_NUMBER() AS tablename
,       ERROR_SEVERITY() AS row_count
,       ERROR_STATE() AS reserved
,       ERROR_MESSAGE() AS data
,       1 AS index_size, 1 AS unused, 1 AS schemaname 
END catch

The results look like this:

Looks great.

29Jun/100
SQL Server CPU Utilization

Some helpful SQL stats from this article.

-- From the SQL Server Performance Dashboard (sys.dm_os_ring_buffers)
-- Query CPU utilization for each four minute intervals
declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
select record_id,
      dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
      SQLProcessUtilization,
      SystemIdle,
      100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
      select
            record.value('(./Record/@id)[1]', 'int') as record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
            timestamp
      from (
            select timestamp, convert(xml, record) as record
            from sys.dm_os_ring_buffers
            where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            and record like '%%') as x
      ) as y
order by record_id desc

-- 4:12 (two procs CPU=8,9,9,9)
DECLARE @count int
SELECT @count =100000000
WHILE @count > 0
	BEGIN
	SELECT @count=@count-1
	--PRINT @count
END
26May/100
Using VBScript to List Processes in Windows

Just like ps, this VBScript will return the running processes. Just like using Task Manager. Very simple.

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

For Each objProcessin objWMIService.InstancesOf("Win32_process")
   WScript.Echo objProcess.Name & vbTab & objProcess.ProcessId
Next

WScript.Echo "End of Process List"

Another method:

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

Set colProcess = objWMIService.ExecQuery _
("Select * from Win32_Process")

For Each objProcess in colProcess
	WScript.Echo objProcess.Name & vbTab & objProcess.ProcessId
Next

WScript.Echo "End of Process List"
6May/100
List Scheduled Tasks from a Windows Server

This script will query a server and output a list of scheduled tasks as a csv (Excel) file. This file is useful when auditing servers.

Note: This script works only for Server 2003, Server 2008, and Windows XP (requires schtasks.exe).


Dim TimeStamp: TimeStamp = Right("0" & Month(now()), 2) & Right("0" & Day(now()), 2) & Right("0" & Year(now()), 2) & Right("0" & Hour(now()), 2) & Right("0" & Minute(now()), 2) & Right("0" & Second(now()), 2)

TmpFile = "C:\Temp\ScheduledTasks\schtasks.txt"
LogFile = "C:\Temp\ScheduledTasks\output_" & TimeStamp & ".csv"

servers = "SERVER-01,SERVER-02,SERVER-03,SERVER-04"
serverArr = Split(servers, ",")

Const ForReading = 1
Const ForWriting = 2

Set objShell = CreateObject("Wscript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

cmdstr = "cmd /c echo >" & TmpFile
retval = objShell.Run(cmdstr, 1, True)

For Each Computer In serverArr
'WScript.Echo Computer

If Computer  "" Then
    cmdstr = "cmd /c schtasks /query /s \\" & Computer & " /v /fo list >>" & TmpFile
Else
    cmdstr = "cmd /c schtasks /query /v /fo list >>" & TmpFile
End If
retval = objShell.Run(cmdstr, 1, True)
Next

Dim objLog: Set objLog = objFSO.CreateTextFile(LogFile)
Set objLog = Nothing
Set objLog = objFSO.OpenTextFile(LogFile, 2)

If objFSO.FileExists(TmpFile) Then
    Dim linecount, objFSO, objFile, ln, strLine

    linecount = 0 
servercount = 0
 
    Set objFile = objFSO.OpenTextFile(TmpFile, ForReading)
    Do Until objFile.AtEndOfStream
        strLine = Trim(objFile.Readline)

        If Left(strLine, 9) = "HostName:" Then
            hostname = Mid(strLine, 39)
            'wscript.echo vbTab & ""
If (servercount = 0) Then 
objLog.Write """HostName"",""TaskName"",""Next Run Time"",""Status"",""Logon Mode"",""Last Run Time"",""Last Result"",""Creator"",""Schedule"",""Task To Run"",""Start In"",""Comment"",""Scheduled Task State"",""Scheduled Type"",""Start Time"",""Start Date"",""End Date"",""Days"",""Months"",""Run As User"",""Delete Task If Not Rescheduled"",""Stop Task If Runs X Hours and X Mins"",""Repeat: Every"",""Repeat: Until: Time"",""Repeat: Until: Duration"",""Repeat: Stop If Still Running"",""Idle Time"",""Power Management"""
End If
objLog.Write vbCrLf
servercount = servercount + 1
     	 objLog.Write """" & hostname & """"
        ElseIf Left(strLine, 9) = "TaskName:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
            'wscript.echo vbTab & vbTab & "" & hostname & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 14) = "Next Run Time:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 7) = "Status:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """" & "," & """" & "Interactive/Background" & """"
     
        ElseIf Left(strLine, 14) = "Last Run Time:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 12) = "Last Result:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 8) = "Creator:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 9) = "Schedule:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 12) = "Task To Run:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 9) = "Start In:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 8) = "Comment:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 21) = "Scheduled Task State:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 15) = "Scheduled Type:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 11) = "Start Time:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 11) = "Start Date:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 9) = "End Date:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 5) = "Days:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
     
        ElseIf Left(strLine, 7) = "Months:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 12) = "Run As User:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 31) = "Delete Task If Not Rescheduled:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 37) = "Stop Task If Runs X Hours and X Mins:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 14) = "Repeat: Every:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 20) = "Repeat: Until: Time:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 24) = "Repeat: Until: Duration:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 30) = "Repeat: Stop If Still Running:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 10) = "Idle Time:" Then
            'wscript.echo vbTab & vbTab & "" & Mid(strLine, 39) & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        ElseIf Left(strLine, 17) = "Power Management:" Then
            'wscript.echo vbTab & ""
objLog.Write "," & """" & Mid(strLine, 39) & """"
        End If
        linecount = linecount + 1
    Loop
    objFile.Close
    Set objFSO = Nothing
 
Else
    wscript.echo "fail: file not found"
End If

WScript.Echo "End"
13Apr/100
Display Process List Using VBScript

This great VBScript script will display running processes on a Windows workstation. The script queries the Win32_Process from a WMI Object using ExecQuery then loops through the results. To access the process list on another server, set the strComputer variable to the server's name.

Option Explicit
Dim objWMIService, objProcess, colProcess, strComputer, strList

strComputer = "."

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colProcess = objWMIService.ExecQuery("Select * from Win32_Process")

For Each objProcess in colProcess
	strList = strList & vbCr & objProcess.Name
Next

WSCript.Echo strList
WScript.Quit
14Dec/090
Finding Duplicate Rows in SQL

I used this to solve a problem today.

// Finding duplicates in a table

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

// Find rows that occur exactly once

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

// Return all duplicate cities and how often they appear
// Works with ORACLE

select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1

// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL

select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc

// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear

select city_name, count(city_name) as cnt
from areas
group by city_name

// version for Micrsoft's MSSQL Server
// make use of the HAVING clause

select city_name
from areas
group by city_name
having count(*) > 1

// multiple columns in MySQL:
SELECT PL_CONTACT_FK, PL_PHONE_FK, COUNT(*) as DuplicateCount FROM PHONE_ALTERNATE GROUP BY PL_CONTACT_FK, PL_PHONE_FK HAVING DuplicateCount > 1;

24Nov/090
Enable Remote Desktop Remotely

For Windows XP:

1. Go to Start, Run and type: regedit
2. In regedit, click File, Connect Network Registry
3. Enter the name or IP address
4. Browse to: HKEY LOCAL MACHINE\SYSTEM\CurrentControlSet\Control\Terminal Server
5. Edit the key: fDenyTSConnections and set it to 0.
6. If TSEnabled is set to 0, then set that to 1.

Tagged as: No Comments
19Nov/090
Send an Email with VBScript

Another really easy script. This script will send an email. As I automate tasks, I like to build an html log file and email the results of the script. You can also send an email as text.

' The following procedure will take an email address and content and complete an email
Sub EmailSend(EmailAddressTo,EmailAddressCc,EmailAddressBcc,EmailAddressFrom,Subject,Content)
	' Send the email report
	Dim cdoMessage, cdoConfig, sch: sch = "http://schemas.microsoft.com/cdo/configuration/" 
	
	Set cdoConfig = CreateObject("CDO.Configuration") 
	
	With cdoConfig.Fields 
		.Item(sch & "sendusing") = 2 ' cdoSendUsingPort 
		.Item(sch & "smtpserver") = "exchangeserver" 
		.Item(sch & "smtpserverport") = 25 
		.update 
	End With 
	
	Set cdoMessage = CreateObject("CDO.Message") 
	
	With cdoMessage 
		Set .Configuration = cdoConfig 
		.From = EmailAddressFrom 
		.To = EmailAddressTo 
		.Cc = EmailAddressCc
		.Bcc = EmailAddressBcc
		.Subject = Subject
		.HTMLBody = Content
		'.TextBody = Content 
		.Send 
	End With 
	
	' Cleanup
	Set cdoMessage = Nothing 
	Set cdoConfig = Nothing 
End Sub

This code will call the sub:

Set LogFile = objFSO.OpenTextFile(sCurPath & "\Logs\" & LogFileName, 1)
Content = LogFile.ReadAll
LogFile.Close 

EmailSend "email@address.com,email2@address.com","ccemail@address.com","bccemail@address.com","fromemail@address.com","Email Subject",Content
12Sep/090
How to Write to the Windows Application Log Using VBScript

This script helped me configure an enterprise monitoring system. I created little events in the Windows event log and monitored the events using the syslog system we implemented. The script creates any id and description you need.

Const EVENT_SUCCESS = 1

Set objShell = Wscript.CreateObject("Wscript.Shell")

objShell.LogEvent EVENT_SUCCESS, "Test error."

WScript.Echo "Complete"
26Aug/090
Retrieve Model and Serial Number with VBScript

Simple script to retrieve information about a workstation or hardware.

strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colSettings = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
For Each objComputer in colSettings 
    Wscript.Echo "System Name: " & objComputer.Name
    Wscript.Echo "System Manufacturer: " & objComputer.Manufacturer
    Wscript.Echo "System Model: " & objComputer.Model
Next

Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM Win32_ComputerSystemProduct") 
For Each objItem in colItems 
    Wscript.Echo "Serial Number: " & objItem.IdentifyingNumber
Next