sp_toptables

Are you managing big databases with a lot of tables like SAP as I'm?Do you use sp_spaceused, as I'm?Do you want to know your n biggest tables as I'm?Then don't search any longer.I'm using this script every day for may maintenance jobs.I generates, like sp_spaceused, out of sysindexes the n biggest tables without any cursor, […]

XML Encode UDF

I routinely need to retrieve and package query result sets in an XML package.  The built in functions supported by SQL Server 2000 are effective, but are often more of a pain than a help.  This routine can be used in a simple select statement to XML encode the contents of a column during the […]

get the column list

This script will get the columnlist for a table parameter

Recursive Factorial Function ( n ! )

Calculates the factorials of numbers between 1 - 31.High level is 31 because maximum stored procedure, function, trigger, or view nesting level is limited to 32.

CreateDBREINDEX

This script will generate a set of DBCC DBREINDEX commandsthat can be executed to rebuild the indexes.  Additional  information is provided to assist with sizing of the fillfactors.

REVISED Dynamic Crosstab Query

Here is a revised version of the crosstab query I submitted last week.  This new version avoids the WHILE loop (which was used in my pervious version) and is thus COMPLETELY SET-ORIENTED for better performance.

Selecting XML out

I have a table with multiple rows for Description belonging to one Heading. For each row of data I need a .The xml should look like this: A heading 234567890123456789010 234567890123456789010 234567890123456789010   ...(to ) A heading 234567890123456789010 234567890123456789010   ...(to )

UDF to get Day of the Year

Add carriage return every nth char

This script will take a string (8000) and add a carriage return at the closest space to nth char. e.g. You only want 50 characters per line. This will add carriage return at the closest space before the 50th character.

Creating a Dynamic Crosstab Query

Crosstab queries (also called "pivot tables") in which you know beforehand the number of column values to aggregate by, can easily be performed in T-SQL using CASE statements wrapped in the SUM function.  Where things get tricky, however, is when you don't know how many aggregation values exist, and you are required to write a […]