My code library
Last updated: November 24th '05 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

This month's 2 click survey:
Is .NET important for a database professional?


 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:
Database coding conventions SQL Server DBA and developer interview questions
Evaluation of federated database servers SQL Server FAQ on programming, designing, administration, tools, replication
SQL Server security best practices SQL Server administration best practices
Related books:
The guru's guide to Transact SQL Advanced Transact-SQL for SQL Server 2000 SQL Server 2000 Programming by example
 
VB code to add SQL Server advanced entries/server aliases
VB code to generate textual graphics
TagIT - A VB tool to HTMLize data
T-SQL code to find out the nth highest number in a column
T-SQL code to send messages/notifications to all logged in users in Windows LAN
T-SQL code to log messages to a text file on the server, from your sotred procedures and SQL scripts
T-SQL code to get the last day of the month!
Procedure to script your data (to generate INSERT statements from the existing data)
User Defined Function (UDF) to add up all the individual digits within a given number
T-SQL code to split a column into two columns in a SELECT statement, based on a condition
T-SQL code to concatenate all the values of a column into one row
T-SQL code to pick a random row from a table using the RAND() function
Stored procedure to generate a simple or complex random password
COM component that can be used in T-SQL to generate CREATE TABLE script
Procedure to import SQL Server error log into a table
User Defined Function (UDF) to convert a given string to proper case
sp_lock2, an enhanced version of sp_lock, that shows detailed lock information
 Stored procedure to track the database file growth percentages and trends by date
The undocumented nature of sp_readerrorlog in SQL Server 2000
Stored procedure to identify the biggest tables in a given database, along with the rowcounts
Code to find out the statement that caused the trigger to fire!
Click here to download Happy Valentines day stored procedure!
Click here to download: Happy holidays stored procedure 1! -- Happy holidays stored procedure 2!
Fun with numbers in T-SQL queries and sample User Defined Functions!
UDF to convert alphabetic phone numbers to numeric
VB code VB code to add SQL Server advanced entries/server aliases  <BACK>
This program is written and tested in VB 6.0 against SQL Server 7.0. It uses registry API to programmatically add SQL Server advanced entries. Actually you should be using a tool called 'Client network utility' that comes with SQL Server 7.0. My code comes in handy when you want to dynamically add the advanced entries from your application.
VB code VB code to generate textual graphics  <BACK>
This program is written and tested in VB 6.0. It is very useful in designing captions/headings for web pages, with shadow effect. By mixing and matching the colors between different layers of text and playing around with the shadow effect, you will be able to come up with cool graphics.
VB code TagIT - A VB tool to HTMLize data  <BACK>
This program is written and tested in VB 6.0. Often your ASP pages pull and display textual data from a backend database. While displaying this data, you may want to add special formatting to specific words or sentences. A common practice which addresses this requirement is -- to store the data in the database along with embedded HTML tags. This tool comes in handy when you want to embed the HTML tags in your textual data, before posting the data into the database. Use this tool on your data and save the output into your database.
T-SQL code T-SQL code to find out the nth highest number in a column  <BACK>
Ever wondered how to find out the second highest salary from the employees table? Or how to find out the third oldest employee in the company? Here is a stored procedure which accepts the table name, column name, and nth number and displays the nth highest number from the given column.
T-SQL code T-SQL code to send messages/notifications to all logged in users in Windows LAN  <BACK>
This stored procedure accepts a message as an input parameter and sends that message to all the currently logged in SQL Server users using NET SEND.
T-SQL code T-SQL code to log messages to a text file on the server, from your stored procedures and SQL scripts  <BACK>
This stored procedure accepts a message, filename, overwrite mode as an input parameters and logs that message to the specified file on the server
T-SQL code T-SQL code to get the last day of the month!   <BACK>
The logic behind this 'last day of the month' calculations is that you get the month part of a given date, add one to it, there by getting to the next month. Then subtract 1 day from the first of that month. This whole Thing can be written in one line and here it is:

SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,GETDATE())),DATEADD(m,1,GETDATE())))
T-SQL code Procedure to script your data (to generate INSERT statements from the existing data)  <BACK>
This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.

Click here to download the procedure! (SQL Server 2000 version)

Click here to download the procedure! (SQL Server 2005 / Yukon version)

Advantages:
  • Data from both tables and views can be scripted
  • No CURSORs are used
  • Table names and column names with spaces are handled
  • All datatypes are handled except images, large text and binary columns with more than 4 bytes
  • NULLs are gracefully handled
  • Timestamp columns are handled
  • Identity columns are handled
  • Very flexible and configurable
  • Non-dbo owned tables are handled 
  • Computed columns are handled
  • You can filter the rows for which you want to generate INSERTs

Usage:

Example 1: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
NOTE: If you have too many columns, you are advised to ommit column list, as shown below, to avoid erroneous results
EXEC sp_generate_inserts 'titles', @Include_Column_List = 0
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4: To generate INSERT statements for 'titles' table for only those titles which contain the word 'Computer' in them:
EXEC sp_generate_inserts 'titles', @From = "from titles where title like '%Computer%'"
Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
NOTE: By default TIMESTAMP column's data is not scripted
EXEC sp_generate_inserts 'titles', @Include_Timestamp = 1
Example 6:  To print the debug information:
EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name:
NOTE: To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8: To generate INSERT statements for the rest of the columns excluding images:
NOTE: When using this otion, DO NOT set @include_column_list parameter to 0
EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9: To generate INSERT statements for the rest of the columns excluding IDENTITY column:
EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10: To generate INSERT statements for the top 10 rows in the table:
EXEC sp_generate_inserts mytable, @top = 10
Example 11: To generate INSERT statements only with the columns you want:
EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12: To generate INSERT statements by ommitting some columns:
EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:
NOTE: The @disable_constraints option will disable foreign key constraints, by assuming that the source data is valid and referentially sound
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14: To avoid scripting data from computed columns:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1

NOTE: Please see the code and read the comments to understand more about how this procedure works!

To generate INSERT statements for all the tables in your database, execute the following query in that database, which will output the commands, that you need to execute for the same:

SELECT 'EXEC sp_generate_inserts ' + 
'[' + name + ']' + 
',@owner = ' + 
'[' + RTRIM(USER_NAME(uid)) + '],' + 
'@ommit_images = 1, @disable_constraints = 1'
FROM sysobjects 
WHERE type = 'U' AND 
OBJECTPROPERTY(id,'ismsshipped') = 0


If the output seems truncated or incomplete in Query Analyzer, click here for a solution!

User Defined Function (UDF) to add up all the individual digits within a given number <BACK>
Use this function to add all the digits in a given number. I worte this just to show how T-SQL can be used and how useful are User Defined Functions.

Here is an example:

SELECT dbo.ADD_DIGITS(id) FROM sysobjects WHERE name LIKE 'ADD%'
T-SQL code to split a column into two columns in a SELECT statement, based on a condition  <BACK>

This query uses a CASE statement within the SELECT statement to split a column into two based on a certain condition.
T-SQL code to concatenate all the values of a column into one row  <BACK>

This trick uses a variable in the SELECT statement and appends all the values of a given column to that variable. I actually picked up this trick from SWYNK mailing list, but I don't remember the author's name anymore. If you know the author or if you are the author of this mailing list posting, please let me know and I will be more than  happy to mention your name here!
T-SQL code to pick a random row from a table using the RAND() function  <BACK>

This example uses the RAND function of SQL Server to pick a random row from the given table. For this code to work properly, you should have a numeric (int) unique key. It's okay to not to have a unique key but an numeric column is essential.
Stored procedure to generate a simple or complex random password  <BACK>

This procedure generates random passwords using RAND() function. It can be configured to generate a simple or a complex password. You can also customize the length of the password generated. Complex passwords will include upper and lower case letters, numbers and special characters. See the code to realize how useful the RAND() function is! When you choose to generate a simple password (default behavior), SPECIAL CARE is taken to generate meaningful/easy to remember passwords.
COM component that can be used in T-SQL to generate CREATE TABLE script  <BACK>

This is a COM component (DLL) written in VB which connects to SQL Server and generates the CREATE TABLE script for a given table from given database. It uses SQL DMO object model to connect to SQL Server. You can use this component from VB or VC application as well as from stored procedures and T-SQL. The Zip file contains the VB code for the COM component, a VB project that shows the usage and a stored procedure sp_scripter which instantiates this component to generate CREATE TABLE scripts. This procedure uses OLE Automation procedures such as sp_OACreate, sp_OADestroy, sp_OAMethod to instantiate the COM object. Unzip the Zip file into a folder and then register the Script.DLL using regsrv32.exe.

Here is an example:
regsvr32 "E:\code\Script.dll"

To unregister the DLL:
regsvr32 "E:\code\Script.dll" /u

Procedure to import SQL Server error log into a table  <BACK>

This procedure reads the SQL Server error log using sp_readerrorlog and imports the error log's contents into a specified table. It accepts three parameters:

@log_name is the name of the table into which the error log will be imported
@log_number is the error log number, 0 is default and refers to current error log
@overwrite -- specify 1 for this parameter, so that the target table could be overwritten, if it already exists

Read the comments section inside the procedure for examples.
User Defined Function (UDF) to convert a given string to proper case  <BACK>

T-SQL has no built-in function to convert a string to proper case (In a proper case string, the first letter of each word would be in upper case. E.g: Bill Gates). So, I ended up writing my own PROPERCASE function.

Example1: To convert the string 'william h gates' to proper case:
SELECT dbo.PROPERCASE('william h gates')
Example2: To convert the Notes field of titles table in pubs database to proper case:
SELECT dbo.PROPERCASE(notes) FROM pubs..titles

Click here to download the equivalent stored procedure if you are running SQL Server 7.0
sp_lock2, an enhanced version of sp_lock, that shows detailed lock information <BACK>

Create this procedure in the master database and call it from the required databases. When you want to see the locks in a particular database, make sure, you are executing this procedure from being in that database.

Here are the enhancements:
  • The program name that is holding the lock is displayed
  • The object name is displayed on which the locks are held
  • Database name in which the locks are held is displayed
  • Using the @dbname parameter you can restrict the displayed lock information to a specific database
  • Using the @spid parameter you can restrict the displayed lock information to a specific spid

Examples are available in the comments section of the stored procedure text.


Disclaimer and terms of use
Copyright © 1997 - 2003 Narayana Vyas Kondreddi. All rights reserved.