1) How to use fully scrollable cursors?

Demonstrates fully-scrollable cursor.sql

2) How to determine the number of triggers & their firing order?

Triggers count & order.sql

3) How to solve some ranking problem? This question was posed in the Winntmag forum. See the thread for more details.

Skating Event - Ranking Problem.sql

4) How to get output value from a dynamic SQL statement?

Getting output value from dynamic SQL statement.sql

5) How to generate a header record for the data in a table that can be BCPed out?

View for BCP out with a header row of the column names.sql

6) How to order values in a sequence using an indexed update?

Ordering by sequence.sql

7) How to get tokens from a delimited string? This also shows how to generate some simple statements using T-SQL rather than coding them manually.

To get tokens from a delimited string.sql

8) How to convert varbinary values into their hexadecimal string representation?

Using xp_varbintohexstr undocumented extended SP.sql

9) How to generate SELECT statements for tables?

Generate select stmt with cols for table.sql

10) How to pipe the RESTORE statement output to a temporary table?

Pipe RESTORE output to temporary table.sql

11) How to determine all the defined identity columns in a database?

Query to determine identity column for a table(s).SQL

12) Information on undocumented DBCC commands. Some of these commands will work in SQL60/65 also. These commands are intended for understanding the internal working of the database system while some are purely academic in nature.

Info on undocumented DBCC commands.txt

13) How to use ODBC style canonical functions in T-SQL? The OLE-DB provider for SQL Server fully supports the ODBC canonical functions. This allows you to for instance encrypt data using the ODBC ENCRYPT function or do other operations. This can be used in distributed queries also.

Undocumented usage of fn escape syntax.sql

14) How to join results of a SP with another table?

Demonstrates how to join results from a SP with a normal table.sql

15) How to get the data for all the saved diagrams in a database? I have a sample script that uses linked server to import diagrams from a different server. This can be moved to a different server using DTS or BCP also.

SELECT statement to get all the data for saved diagrams in a database.sql

16) How to get records with the TOP 2 dates in a set of values?

Get records with top 2 dates in a set of values.sql

17) How to generate random values using RAND & NEWID function?

Generating random values using RAND function.sql

18) How to determine the size taken by the auto-statistics in a database?

Auto-statistics size from sysindexes.sql

19) How to set user-defined counters?

Example of using user-defined counters.sql

20) How to pad decimal values with zeroes & to a fixed length for say exporting the data?

Left & Right pad decimal values with zeroes in character format.sql

21) This script demonstrates how to use the getchecksum function. This is undocumented & i do not recommed using it. This is provided for the curious folks who wants to know how this function works. This function is also used in the replication procedures.

Undocumented getchecksum function example.sql

22) How to evalulate an expression in a string & get the value?

Evaluate the value of an expression in a string.sql

23) How to perform SELECT..INTO type of queries for results from a SP? This is useful in cases where you do not know the metadata of the result set & do not want to write the CREATE TABLE statement before doing the INSERT..EXEC. This can be used in a easy way to say save the data from a SP in a table.

Perform SELECT INTO for the result set of a SP.sql

24) This script shows that a primary data file is required for a active log backup. Keep this in mind when recovering suspect databases, hardware failures etc. For more details, see a discussion on the same in the Windows 2000 Magazine SQL Server forum.

Demonstrate primary data files requirement for log backups in case of disaster.sql

25) This sample script shows how to validate the a GUID value in a string. This can be used to ensure that the value is in the correct format & with only hex digits.

Routine to validate GUID values in string.sql

26) Code to convert a hexadecimal value stored in a string to integer.

HEX string value to int conversion.sql

27) Script to simplify creation of backup files with timestamp just like the SQLMAINT utility. This script also does differential backups during the week & full backup on sundays. You can use this in a job to automate the backup process quite easily & add your own administrative routines.

Script to do Differential backups on weekdays, Full on sundays & filenames with timestamp info.sql

28) This example shows how to read, write to the NT registry from T-SQL. Please note that these SPs are undocumented & hence should not be used in any production code.

Undocumented SPs to read & write Registry values.sql

29) Statements to list the filegroup on which a table resides in a database. This can be used in administrative routines.

List filegroups on which the tables are stored in a database.sql

30) How can you check if a row(s) is locked or not & take action appropriately? The examples in the script use the new optimizer hint READPAST to achieve the result. A method using the lock timeout mechanism is also shown for completeness. These methods will allow the required SPs to check for locked rows & then take action rather than waiting for the lock to get released.

How to check for a locked row or rows.sql

31) How to determine if a system table is materialized internally by SQL Server or if it is stored in the database?

Determine if a system table is fake i.e., materialized or not.sql

32) This script shows several solutions to solve a problem relating to counting. Analysis of the solutions is left to the reader but sample output from several runs are shown & the solutions are analyzed in the script.

Performance Analysis of 3 solutions for a problem.sql

33) This sample script shows how to use the COLUMNS_UPDATED() function to check for update of specific column(s). This generic approach can be used on tables with more than 8 columns. The required formula for calculating the mask & getting the corresponding byte from the function is also explained in the script.

Usage of COLUMNS_UPDATED() for more than 8 columns.sql

34) How to generate a simple ALTER TABLE script to disable all FK constraints in a database?

Generate ALTER script to disable all FK constraints.sql

35) How to get a random row from duplicates?

Getting only an arbitrary row for each ID.sql

36) This script shows how to use a cursor to store the @@IDENTITY values inside trigger & use them later in the calling SPs.

How to avoid the @@identity problems.sql

37) How to use the INFORMATION_SCHEMA views & the meta-data functions to determine all the primary keys in a database?

Determine primary key for all tables in a database.sql

38) This script can be used to delete orphaned users in a restored database on a different Server. This can happen due to missing logins in the new server for instance.

Delete Orphaned Users from a Restored database.sql

39) T-SQL string manipulation functions are limited yet powerful if used properly. This script shows how to count occurrences of a string / character in a string, find the number of values in a delimited string & so on.

Count Occurences in a String & Others.sql

40) A sample script that shows the new INDEXPROPERTY metadata function usage. This script produces several interesting reports!

INDEXPROPERTY metadata function examples.sql

This page was last updated on April 28, 2006 05:15 PM.