How to create Insert Script in SQL Server database

How to create Insert Script in SQL Server database===========================================
CREATE PROC sp_generate_inserts( @table_name varchar(776), — The table/view for which the INSERT statements will be generated using the existing data @target_table varchar(776) = NULL, — Use this parameter [...]

FOR XML EXPLICIT

In Sql FOR XML command can be used in different way..
The most flexible is FOR XML EXPLICIT.
This command option used to generate XML from table
Cosider one table named tblProductMaster , the structure of table is
tblProductMaster (ID , ProductName)
Now write the following command
SELECT 1 AS TAG,
NULL AS PARENT,
ID AS [Product!1!ID!ELEMENT],
ProductName AS [Product!1!ProductName!ELEMENT]
FROM tblProductMaster FOR XML EXPLICIT
It will [...]

SQL Server function for Count the Occurrence of Character in the string

SQL Server function for Count the Occurrence of Character in the string
============================================================
– Author : Abhishek Joshi
– Date : 30 July, 2007
– Desc : Count the Occurrence of Character in the string
– Usage : select dbo.GetOccurrence(‘Abhishek Joshi’,’a’)
CREATE FUNCTION [dbo].[GetOccurrence] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN
DECLARE @vInputLength INT
DECLARE @vIndex [...]

Declare Cursor and Open for manipulation

———————————————————
TO DECLARE CURSOR AND OPEN IT TO UPDATE AND CLOSE CURSOR
———————————————————
DECLARE cur_price CURSOR
FOR SELECT PRICE,RATE,AMOUNT
FROM TBLPRICE
OPEN cur_price FETCH NEXT FROM cur_price
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TBLPRICE SET PRICE = (PRICE – 100) WHERE CURRENT OF cur_price
FETCH NEXT FROM cur_price
END
CLOSE cur_price
DEALLOCATE cur_price
GO

To find Column in all relevant tables !…

copy this query and paste it into your query analyser
just replace column name as STATEID and run the query…
It will return all table name and column name that matches this column name
SELECT SYSCOLUMNS.NAME,SYSOBJECTS.NAME FROM SYSCOLUMNS
JOIN SYSOBJECTS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
WHERE SYSCOLUMNS.NAME LIKE ‘%STATEID%’ AND  SYSOBJECTS.XTYPE = ‘U’

ProperCase User Defined Function for SQL Server

ProperCase User Defined Function for SQL Server
=========================================
/*************************************************************************************************
Purpose: To convert a given string to proper case
Written by: Abhishek Joshi
Tested on: SQL Server 2000
Examples:
To convert the string ‘william h gates’ to proper case:
SELECT dbo.PROPERCASE(‘william h gates’)
To convert the Notes field of titles table in pubs database to [...]

Use Index and Cursor in SQL Server 2000

Using Index
================
select *from ZIPCodes where StateName = ‘New York’
– Create Index
create nonclustered index idxStateName on ZIPCodes(StateName)
create nonclustered index idxZIPType on ZIPCodes(ZIPType)
– Use Index
select *from ZIPCodes with(INDEX(idxZIPType)) where ZIPType = ‘S’
– List of Indexes on Perticular Table
exec sp_helpindex ‘ps_client_master’
– Drop Index
drop index ps_client_master.ps_client_master_Index_1
Using Cursors
===============
DECLARE @ClientID char(11)
declare cl CURSOR FOR
select int_id from [...]