SQL NET - A Place to talk on MS SQL Server and other database tools

How to Create an SSAS 2008 Hierarchy

2010-04-21T02:52:00.000+05:30

Let me explain the with a simple date dimension example first. Let me also assume you are familier on how to create a data source, then a datasource view, and then create a cube with cube wizard. Then comes the attribute part. In SSAS 2008 BI Studio, only the key column will come by default as an attribute for a dimension.

So we can see sk_DateID as a single dimension. Double clickon th date dimension from the solution explorer, then you may have three panes ( in a tree view) . Attribute , Hierarchy and Data source view.

In the data source view, all the available columns from the dimension will be displayed. Right click on any columns and click –New attribute from Column.

This will create an attribute to the dimsnion. We can see it gets added in th attribute pane. Lets add MonthName, YearNumber And QuarterNumber .

Right Click on the MonthName attribute – properties. Change the Key column attribute. Add MonthNumber and YearNumber to the collection. Change the Namecolumn to MonthName and Value column to monthNumber.

Rightclick on the quarter attribute , Change the key column to QuarterNumber and YearNumber. Name column and Value Column to QuarterNumber

So now we have set the Attributes. Next step is to create attribute relationship. Take the “Attribute relationship” tab. Rt click on the sk_DateID . Click on New relationship, Select DateValue from the rightside combobox in the dialogue box. Give Ok.

Right click on DateValue capsule box – New relationship, Select MonthNumber,

Repeat the same for linking MonthNumber with quarterNumber and QuarterNumber with YearNumber. Finally the attribute relationship page will like below image.

Now go back to dimenionStructure tab. Drag Year attribute to the hierarchy tab, Then drag quarter just below, then month, then date. Now we are ready with Data dimension with a simple hierarchy and attributes.

Converting Column data into a single Row

2010-03-30T02:51:00.001+05:30

STUFF(( SELECT DISTINCT '],[' + ltrim(str(YEAR(OrderDate)))

FROM Sales.SalesOrderHeader

ORDER BY '],[' + ltrim(str(YEAR(OrderDate)))

FOR XML PATH('')

), 1, 2, '') + ']'

Rows to Column transpose

2009-12-28T16:56:00.001+05:30

SELECT [id]

,[nam]

,[city]

FROM [aaaa]

DECLARE @str VARCHAR(100)

SELECT @str = COALESCE(@str + ' ,', '') + [city]+[nam] FROM [aaaa]

SELECT category= @str

-- contributed by Sheju Gangadharan

Tips - Grant and Revoke

2009-12-18T16:57:00.001+05:30

SQL Server offers three pretty simple commands to give and remove access, these commands are:

GRANT - gives a user permission to perform certain tasks on database objects
DENY - denies any access to a user to perform certain tasks on database objects
REVOKE - removes a grant or deny permission from a user on certain database objects

Here are some examples of these commands.

Allow users Joe and Mary to SELECT, INSERT and UPDATE data in table Customers

GRANT INSERT, UPDATE, SELECT ON Customers TO Joe, Mary

Revoke UPDATE access to table Customers for user Joe

REVOKE UPDATE ON Customers to Joe

DENY DELETE access to table Customers for user Joe and Mary

DENY DELETE ON Customers to Joe, Mary

As you can see from the above examples it is pretty easy to grant, deny and revoke access. In addition to grant SELECT, INSERT, DELETE and UPDATE rights you can also grant EXECUTE rights to run a stored procedure as follows:

GRANT EXEC ON uspInsertCustomers TO Joe

To determine what rights have been granted in a database use the sp_helprotect stored procedure.

In addition to granting rights to objects that you create you can also grant users permissions to do other tasks such as create tables, views, stored procedures, etc... To grant a user permissions to create a table you would run this command.

GRANT CREATE TABLE TO Joe

As you can see granting rights and permissions to certain features is not all that difficult to do. Take the time to understand what permissions are really needed by the database users and grant, deny and revoke accordingly instead of just using the default database roles.

Next Steps

Take a look at this tip on how to determine what permissions have been granted Auditing your SQL Server database and server permissions
Take a look at these commands for other examples
- GRANT
- DENY
- REVOKE
- sp_ helprotect

SYSOBJECTs-List All Database Objects in SQL Server

2009-12-18T15:15:00.001+05:30

SYSOBJECTs-List All Database Objects in SQL Server

Below statemnent will list all tables int he current database.

USE YourDBName

1) SELECT * FROM sys.Tables

2) SELECT

* FROM information_schema.Tables

Also, sysobjects, syscolumns and systypes are the database objects that we would be using here.

Sysobjects contains information about each object in the database. this includes the object name, the user id of the user who created this object and many other useful information.

select * from sysobjects;

The most useful column in this table is the type column. This column signifies what type of object is in the returned rowset. specifying

select * from sysobjects where type = 'u' ;

will return the list of tables on the current database. Here's a list of possible values for xtype:

C : CHECK constraint
D : Default or DEFAULT constraint
F : FOREIGN KEY constraint
L : Log
P : Stored procedure
PK : PRIMARY KEY constraint (type is K)
RF : Replication filter stored procedure
S : System tables
TR : Triggers
U : User table
UQ : UNIQUE constraint (type is K)
V : Views
X : Extended stored procedure
TF : Functions

You can use syscolumns to retrieve columns on the database. doing

select * from syscolumns

would return a result containing column information which you can use to determine the scale, data type, precision and etc. The xtype on the syscolumns table acts differently from the sysobjects. the xtype column here represents the datatatype of that column. running this script:

select * from syscolumns where xtype = 167;

will return all columns that has a datatype of varchar. heres a list of possible values for this xtype column:

34 image

35 text

36 uniqueidentifier

48 tinyint

52 smallint

56 int

58 smalldatetime

59 real

60 money

61 datetime

62 float

98 sql_variant

99 ntext

104 bit

106 decimal

108 numeric

122 smallmoney

127 bigint

165 varbinary

167 varchar

173 binary

175 char

189 timestamp

231 nvarchar

239 nchar

241 xml

231 sysname

I got this xtypes by running : select xtype, name from systypes; which basically contains a list of available sql datatypes.

Insert Generator SP - SQL Server

2009-12-18T15:13:00.001+05:30

alter PROC InsertDataGenerator

(@tableName varchar(100)) as

BEGIN

--Declare a cursor to retrieve column specific information

--for the specified table

DECLARE cursCol CURSOR FAST_FORWARD FOR

SELECT column_name,data_type FROM information_schema.columns

WHERE table_name = @tableName

OPEN cursCol

DECLARE @string nvarchar(3000) --for storing the first half

--of INSERT statement

DECLARE @stringData nvarchar(3000) --for storing the data

--(VALUES) related statement

DECLARE @dataType nvarchar(1000) --data types returned

--for respective columns

SET @string='INSERT INTO '+@tableName+'('

SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0

begin

print 'Table '+@tableName+' not found, processing skipped.'

close curscol

deallocate curscol

return

END

WHILE @@FETCH_STATUS=0

BEGIN

IF @dataType in ('varchar','char','nchar','nvarchar')

BEGIN

SET @stringData=@stringData+'''''''''+

isnull('+@colName+','''')+'''''',''+'

END

ELSE

if @dataType in ('text','ntext') --if the datatype

--is text or something else

BEGIN

SET @stringData=@stringData+'''''''''+

isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'

END

ELSE

IF @dataType = 'money' --because money doesn't get converted

--from varchar implicitly

BEGIN

SET @stringData=@stringData+'''convert(money,''''''+

isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'

END

ELSE

IF @dataType='datetime'

BEGIN

SET @stringData=@stringData+'''convert(datetime,''''''+

isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'

END

ELSE

IF @dataType='image'

BEGIN

SET @stringData=@stringData+'''''''''+

isnull(cast(convert(varbinary,'+@colName+')

as varchar(6)),''0'')+'''''',''+'

END

ELSE --presuming the data type is int,bit,numeric,decimal

BEGIN

--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'

SET @stringData=@stringData+''''+'''+isnull(convert(varchar(200),'+@colName+'),''NULL'')+'',''+'

END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @colName,@dataType

END

CLOSE cursCol

DEALLOCATE cursCol

DECLARE @Query nvarchar(4000) -- provide for the whole query,

-- you may increase the size

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ')

VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')''

FROM '+@tableName

exec sp_executesql @query --load and run the built query

--select @query

END

SQL PUZZLE:UPDATING 0 AS 1 AND VICE VERSA IN A BITFIELD

2009-08-14T16:40:00.001+05:30

assume a table has a bit column , how will you update all the data that all 1's should be changed as 0 and vice versa

create table #sss(aa int,bb bit)

Insert Into #sss values(1,0)

Insert Into #sss values(2,1)

ANSWER 1.

update #sss set bb = Case bb when 1 then 0 else 1 end --RANJITH P

ANSWER 2.

Update #sss Set bb =Null Where bb=0

Update #sss Set bb =0 Where bb=1

Update #sss Set bb =1 Where bb is null ----- BINEESH THOMAS

--MAJITHA KV

ANSWER 3.

Update #sss set bb =(Select bb from #sss b where aa in (select aa from #sss c where bb <> a.bb))

from #sss a --MAJITHA KV

To get number of actual decimal places for a number - Optimised By Chacko Abraham

2009-07-13T13:45:00.001+05:30

Select len(cast(floor(reverse(4.23400 - floor(4.23400 ))) as Int) )

To get number of actual decimal places for a number

2009-07-13T12:53:00.001+05:30

Select Len(Cast(Reverse(Substring(Cast(4.23400 as varchar(50)), patindex('%.%', Cast(4.23400 as varchar(50)))+1,100)) as Int))

Getting Comma seperated string from data in an sql Table column

2008-10-03T16:49:00.001+05:30

Declare @Var Varchar(10)
Select @Var = COALESCE(@Var+',' , ' ') + Col1 FROM Tab_Name
SELECT @Var

.
===============================================
The content of this message may contain the private views and opinions of the sender and does not constitute a formal view and/or opinion of the company unless specifically stated.

The contents of this email and any attachments may contain confidential and/or proprietary information, and is intended only for the person/entity to whom it was originally addressed. Any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this email in error please notify the sender immediately by return e-mail and delete this message and any attachments from your system.

Please refer to http://www.newmont.com/en/disclaimer for other language versions of this disclaimer. ================================================

How to stop SSRS service from application ?

2008-08-07T10:09:00.001+05:30

I want to stop the execution of reporting service explicitly from my application. How can we do this ?- Abdull Kalam ,. Banglore

You can use dos commands "net stop <service name>" for stopping a service and "net start <service name >" for starting a service

So for default instance SSRS revice it will be "net stop reportserver"
You can use ShellExecute () or similar functions to call it from any front end application.

Get foreign key hierarchy of all DB tables- Omri Bahat

2008-07-17T10:58:00.001+05:30

/*

NAME:                Get foreign key hierarchy of all DB tables
                     (to determine tables INSERT or DROP order, for example)

DESCRIPTION:         This is a short script that returns all table names
                     in the current database, together with their foreign key (FK)
                     hierarchy level, and the table(s) that they reference (when
                     applicable). The value of the FK hierarchy associated with
                     each table is determined as follows: If a table does not
                     have a FK constraint (i.e., it does not reference any other
                     tables via a FK, or in other words - the table is not a foreign
                     table in any FK relationship), then it is of level 0 in the
                     hierarchy. If the table references one or more tables,
                     which do not reference any other tables, then the current
                     table is of level 1, and so on. The tables referenced by
                     each FK (i.e., primary tables) are returned by the script
                     as well, for each FK relationship found. Moreover, if a table
                     references itself (and no other tables), then it is
                     considered as a level 0 table.

                     The script is useful when one wishes to INSERT data into
                     several tables, or DROP tables, and needs to determine the
                     table order to follow - tables of hierarchy 0 must be
                     inserted into first, then those of hierarchy 1, and so on.
                     Similarly, tables with the highest hierarchy should be dropped
                     first, and those with hierarchy 0 should be dropped last.

                     To return the table FK hierarchy info, the script uses the
                     following algorithm: First, get all DB tables that do not
                     have any FK constraints. Then get all tables that have a
                     FK that only reference one or more of the tables that don't
                     have any FKs. Then, get the tables that have FKs mapped
                     to the already collected tables, and so on. The entire
                     algorithm is run in a simple WHILE loop.

USER PARAMETERS:     NA

RESULTSET:           TableName, HierarchyLevel, FKName, FKReference (the primary
                     table in the FK relationship, where applicable)

RESULTSET SORT:      NA

USING TABLES/VIEWS: INFORMATION_SCHEMA.TABLES
                     sysreferences

REVISIONS

-- Get FK hierarchy of all DB tables

SET NOCOUNT ON

DECLARE @i INT
DECLARE @Cnt INT

-- The variable @i is the hierarchy level.
-- The variable @Cnt hold the number of tables returned in the
-- last run of the loop, which tells when the loop should exist.

SET @i = 0
SET @Cnt = 1

IF OBJECT_ID('tempdb..#tblFKTableOrder', 'U') IS NOT NULL
        DROP TABLE #tblFKTableOrder

CREATE TABLE #tblFKTableOrder (
        TableName NVARCHAR(128),
        HierarchyLevel INT,
        FKName NVARCHAR(128),
        FKReference NVARCHAR(128))


-- First, grab all the tables that don't have any FK constraints, as hierarchy level 0.

INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT TABLE_NAME, @i, N'', N''
FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK)
WHERE TABLE_TYPE = 'BASE TABLE'
        AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasForeignKey') = 0

-- Second, get all tables that only have self-referencing (and no other) FKs.
-- In the query below - RS1 contains all table names that references themselves
-- (and possible other tables), and RS2 contains all tables that reference other tables.
-- The desired tables are all those in RS1 that are not in RS2.

INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
SELECT OBJECT_NAME(RS1.fkeyid), @i, OBJECT_NAME(RS1.constid), OBJECT_NAME(RS1.rkeyid)
FROM    (SELECT fkeyid, constid, rkeyid
        FROM sysreferences WITH (NOLOCK)
        WHERE rkeyid = fkeyid ) RS1
        LEFT OUTER JOIN
        (SELECT DISTINCT fkeyid
        FROM sysreferences WITH (NOLOCK)
        WHERE fkeyid <> rkeyid ) RS2
        ON RS1.fkeyid = RS2.fkeyid
WHERE RS2.fkeyid IS NULL

-- Now, drill down in the FK hierarchy. Get all tables
-- that have a FK that references one or more tables in #tblFKTableOrder,
-- yet only references tables that are in #tblFKTableOrder(!), and that have not yet
-- been recorded in #tblFKTableOrder. Tables that reference themselves, as well
-- as tables in #tblFKTableOrder, are considered as well.
-- This is done in a loop, and the loop terminates when we reach the lowest level
-- in the hierarchy (i.e., when no more tables meet the listed condition).

WHILE @Cnt > 0
BEGIN
        -- Analyze the next level in the hierarchy.
        SET @i = @i + 1

        -- Get all tables that reference tables that are recorded
        -- in #tblFKTableOrder (can also reference themselves),
        -- and do not references tables that
        -- were not yet recorded.
        -- This is done by as follows:
        -- RS1 conatains the tables that have FK constraints
        -- that reference tables in #tblFKTableOrder (and possibly have
        -- a self-reference). RS2 contains all tables that reference tables
        -- that are not yet in #tblFKTableOrder (excluding self-refences).
        -- We write into #tblFKTableOrder the tables in RS1, which are
        -- not in RS2.

        INSERT INTO #tblFKTableOrder (TableName, HierarchyLevel, FKName, FKReference)
        SELECT OBJECT_NAME(a.fkeyid), @i, OBJECT_NAME(a.constid), OBJECT_NAME(a.rkeyid)
        FROM sysreferences a
                INNER JOIN
                (SELECT DISTINCT z.fkeyid
                FROM sysreferences z WITH (NOLOCK)
                        INNER JOIN #tblFKTableOrder y WITH (NOLOCK)
                        ON OBJECT_NAME(z.rkeyid) = y.TableName
                        LEFT OUTER JOIN #tblFKTableOrder v WITH (NOLOCK)
                        ON OBJECT_NAME(z.fkeyid) = v.TableName
                WHERE v.TableName IS NULL) RS1
                ON a.fkeyid = RS1.fkeyid
                LEFT OUTER JOIN
                (SELECT DISTINCT x.fkeyid
                FROM sysreferences x WITH (NOLOCK)
                        LEFT OUTER JOIN #tblFKTableOrder w WITH (NOLOCK)
                        ON OBJECT_NAME(x.rkeyid) = w.TableName
                WHERE x.fkeyid <> x.rkeyid
                        AND w.TableName IS NULL) RS2
                ON RS1.fkeyid = RS2.fkeyid
        WHERE RS2.fkeyid IS NULL

        SET @Cnt = @@ROWCOUNT
END

SET NOCOUNT OFF

SELECT * FROM #tblFKTableOrder
ORDER BY HierarchyLevel ASC, TableName ASC, FKName ASC
GO

Simple way to add linked server

2008-07-09T16:40:00.001+05:30

Adding

EXEC master.dbo.sp_addlinkedserver @server = N'AnyName',
@srvproduct=N'ServerName\Instance', @provider=N'SQLOLEDB', @datasrc=N'ServerName\Instance',
@catalog=N'DBNAME'

Executing

Select * from AnyName.DBNAME.dbo.TableName

Finding SQL Server product information

2008-07-08T11:25:00.000+05:30

Product Information
EXEC xp_msver
Edition Information
Select @@VERSION

Collation Issues Handling

2008-07-02T17:26:00.000+05:30

--FINDING COLLATION
--server level collation
select SERVERPROPERTY ('collation')
--DB level collation
select convert(sysname,DatabasePropertyEx(db_name(),'Collation'))
--Table level collation
select name, collation from syscolumns where [id]=object_id('Dim_ItemType')

--CHANGING COLLATION TABLE LEVEL
Alter table Dim_ItemType alter column ItemTypeName varchar(200) COLLATE Latin1_General_CI_AS NULL
--CHANGING COLLATION DB LEVEL
Alter database testNorwegian collate Danish_Norwegian_CI_AI

Finding Cumulative total using SQL Query

2008-07-01T17:49:00.001+05:30

A table contains

Create Table #tab (SlNo Int, Name char(1),Amt Int, CAmt Int)
Insert Into #tab Values(1,'a',10,Null)
Insert Into #tab Values(2,'b',5,Null)
Insert Into #tab Values(3,'c',8,Null)
Insert Into #tab Values(4,'d',3,Null)
Insert Into #tab Values(5,'e',15,Null)

Display cumulative figure like below using a single query

Slno     Name Amt CumulativeAmt
1        a        10           10
2        b        5            15
3        c        8            23
4        d        3            26
5        e        15            41

Solution1 :
Select t1.Slno,t1.name,t1.amt,sum(t2.amt) CumAmount
From #Tab t1,#Tab t2 Where t1.slno>=t2.slno
group by t1.slno,t1.name,t1.amt

Solution 2:
Select Slno, ( Select sum(t2.amt) From ,#Tab T2 where T2.Slno <= T1.Slno )
from #Tab T1

Generate Foreign Key- Sreenivasan

2008-06-13T11:03:00.001+05:30

EXEC DBO.SPGetForeignKeyInfo
IF EXISTS (
    SELECT *
        FROM dbo.sysobjects
        WHERE id = OBJECT_ID(N’[dbo].[SPGetForeignKeyInfo]‘)
            AND OBJECTPROPERTY(id, N’IsProcedure’) = 1)
DROP PROCEDURE dbo.SPGetForeignKeyInfo
GO
CREATE PROCEDURE DBO.SPGetForeignKeyInfo
            AS
            /*
    Author : Seenivasan
This procedure is used for Generating Foreign Key script.
*/
    SET NOCOUNT ON
DECLARE @FKName NVARCHAR(128)
DECLARE @FKColumnName NVARCHAR(128)
DECLARE @PKColumnName NVARCHAR(128)
DECLARE @fTableName NVARCHAR(128)
DECLARE @fUpdateRule INT
DECLARE @fDeleteRule INT
DECLARE @FieldNames NVARCHAR(500)
CREATE TABLE #Temp(
                PKTABLE_QUALIFIER NVARCHAR(128),
                PKTABLE_OWNER NVARCHAR(128),
                PKTABLE_NAME NVARCHAR(128),
                PKCOLUMN_NAME NVARCHAR(128),
                FKTABLE_QUALIFIER NVARCHAR(128),
                FKTABLE_OWNER NVARCHAR(128),
                FKTABLE_NAME NVARCHAR(128),
                FKCOLUMN_NAME NVARCHAR(128),
                KEY_SEQ INT,
                UPDATE_RULE INT,
                DELETE_RULE INT,
                FK_NAME NVARCHAR(128),
                PK_NAME NVARCHAR(128),
                DEFERRABILITY INT)
DECLARE TTableNames CURSOR FOR
SELECT name
    FROM sysobjects
    WHERE xtype = ‘U’
OPEN TTableNames
            FETCH NEXT
    FROM TTableNames
            INTO @fTableName
WHILE @@FETCH_STATUS = 0
    BEGIN
    INSERT #Temp
        EXEC dbo.sp_fkeys @fTableName
                FETCH NEXT
        FROM TTableNames
                INTO @fTableName
    END
            CLOSE TTableNames
            DEALLOCATE TTableNames
SET @FieldNames = ”
SET @fTableName = ”
SELECT DISTINCT FK_NAME AS FKName,FKTABLE_NAME AS FTName,
    @FieldNames AS FTFields,PKTABLE_NAME AS STName,
    @FieldNames AS STFields,@FieldNames AS FKType
            INTO #Temp1
    FROM #Temp
    ORDER BY FK_NAME,FKTABLE_NAME,PKTABLE_NAME
DECLARE FK_CUSROR CURSOR FOR
SELECT FKName
    FROM #Temp1
OPEN FK_CUSROR
            FETCH
    FROM FK_CUSROR INTO @FKName
WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE FK_FIELDS_CUSROR CURSOR FOR
    SELECT FKCOLUMN_NAME,PKCOLUMN_NAME,UPDATE_RULE,DELETE_RULE
        FROM #TEMP
        WHERE FK_NAME = @FKName
        ORDER BY KEY_SEQ
    OPEN FK_FIELDS_CUSROR
                FETCH
        FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
        @fUpdateRule,@fDeleteRule
    WHILE @@FETCH_STATUS = 0
        BEGIN
        UPDATE #Temp1 SET FTFields = CASE WHEN LEN(FTFields)
                = 0 THEN   ‘['+@FKColumnName+']‘
            ELSE FTFields
                        +‘,['+@FKColumnName+']‘ END
            WHERE FKName = @FKName
        UPDATE #Temp1 SET STFields = CASE WHEN LEN(STFields)
                = 0 THEN   ‘['+@PKColumnName+']‘
            ELSE STFields
                        +‘,['+@PKColumnName+']‘ END
            WHERE FKName = @FKName
                    FETCH NEXT
            FROM FK_FIELDS_CUSROR INTO @FKColumnName,@PKColumnName,
            @fUpdateRule,@fDeleteRule
        END
    UPDATE #Temp1 SET FKType = CASE WHEN @fUpdateRule = 0
                    THEN   FKType + ‘ ON UPDATE CASCADE’
        ELSE FKType END
        WHERE FKName = @FKName
    UPDATE #Temp1 SET FKType = CASE WHEN @fDeleteRule = 0
                    THEN   FKType + ‘ ON DELETE CASCADE’
        ELSE FKType END
        WHERE FKName = @FKName
                CLOSE FK_FIELDS_CUSROR
                DEALLOCATE FK_FIELDS_CUSROR
                FETCH next
        FROM FK_CUSROR INTO @FKName
    END
            CLOSE FK_CUSROR
            DEALLOCATE FK_CUSROR
SELECT ‘ALTER TABLE [dbo].['+FTName+'] ADD
CONSTRAINT ['+FKName+'] FOREIGN KEY (’+FTFields+‘)
REFERENCES ['+STName+'] (’+STFields+‘) ’+FKType
    FROM #Temp1
SET NOCOUNT OFF
RETURN
GO

With Regards,

Bineesh Thomas

Project Manager

BDTech – ABS Phone : +91 484 4028707

No.6A, KG Oxford Bussiness Centre, Mobile: +91 9961187103

Sreekandath Road ,Cochin , 682 016, India Email : bineesh.thomas@agricbs.com

SQL Management Studio Shortcuts

2008-06-13T11:02:00.001+05:30

2000 – isqlw –To open a query analyzer in 2000

2005 – sqlwb—To open SSMS in 2005 (Not Query analyzer For New Query use Alt+N)

2005 –ssmsee—TO open SSMS in 2005 (Express Edition)

The most commonly used keyboard shortcuts in the SQL Server Management Studio (SSMS).

Run and then SqlWb.exe to launch SQL Server Management Studio

Results in Grid
Ctrl + D

Change database
Ctrl + U

Results in Text
Ctrl + T

Execute Query
Ctrl + E or F5

Show/Hide Object browser
F8 (Auto Hide has to be enabled)

New Query Window
Ctrl + Q

New Query with New Connection
Ctrl + N (Then selecting SQL Server Query template)

Open a .SQL Script file
Ctrl + O

Full Screen
Shift + Alt + Enter

Parse the query
Ctrl + F5

Show/Hide Results Pane
Ctrl + R

Switch between query and results panes
F6

Information about all the objects in the current database
Alt + F1

Back up History of a DB

2007-12-11T10:10:00.001+05:30

Select backup_set_id, backup_finish_date, name from
msdb..backupset where database_name = N'myDataBase'
and type = 'D' order by backup_start_date desc

CTE is faster than #tables - SQL Server 2005 Optimisation

2007-11-28T10:44:00.000+05:30

Common Table Expression

New in SQL 2005 are Common Table Expressions, CTE for short. A Common Table Expression is an expression that returns a temporary result set from inside a statement. This result set is similar to a hybrid Derived Table meets declared Temporary Table. The CTE contains elements similar to both. Some of the most frequent uses of a Common Table Expression include creating tables on the fly inside a nested select, and doing recursive queries. Common Table Expressions can be used for both selects and DML statements. The natural question is, if we have been using TSQL for this long without Common Table Expressions, why start using them now? There are several benefits to learning CTEs. Although new to SQL Server, Common Table Expressions are part of ANSI SQL 99, or SQL3. Therefore, if ANSI is important to you, this is a step closer. Best of all, Common Table Expressions provide a powerful way of doing recursive and nested queries in a syntax that is usually easier to code and review than other methods. – courtesy dbjournal

Case 1 – Temp table

Select userCode,userName into #tmp from absUser

Select * from #tmp

Case 2 – Using CTE

WITH MyCTE( userCode,userName) AS

(

SELECT userCode,userName

FROM absUser

)

SELECT * FROM MyCTE

Limitation

WITH MyCTE( userCode,userName) AS

(

SELECT userCode,userName

FROM absUser

)

SELECT * FROM MyCTE

This will give an error, CTE will work only the next statement after the creation.

Some Utilities from SQLServer

2007-11-16T11:08:00.001+05:30

For Bulk Export/Import

bcp{[[database_name.][owner].]{table_name | view_name} | "query"}
    {in | out | queryout | format} data_file
    [-m max_errors] [-f format_file] [-e err_file]
    [-F first_row] [-L last_row] [-b batch_size]
    [-n] [-c] [-w] [-N] [-V (60 | 65 | 70)] [-6]
    [-q] [-C code_page] [-t field_term] [-r row_term]
    [-i input_file] [-o output_file] [-a packet_size]
    [-S server_name[\instance_name]] [-U login_id] [-P password]
    [-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]

For Interacting with Windows commands or to execute an external application including ftp

xp_cmdshell

How to change an unknown SQL Server password?

2007-11-16T10:57:00.001+05:30

The password is stored in master.dbo.sysxlogins in the field password, but it is encoded. You could call the stored procedure sp_password and change the password if you had a connection to the SQL Server. Syntax is EXEC sp_password NULL, 'NewPW', 'Username'

Kind Regards,

Bineesh Thomas

_______________________________________________________________

SQL Server Back up 5 – Red gate

2007-05-29T10:23:00.000+05:30

I compared the performance (Size and Time) on a small/medium scale production database (SQL Server 2005)

Full Back up using Sql server management studio

SIZE : 61,223 KB

TIME : About 55 seconds

Back up using Red Gate Back Up tool

SIZE : 12,221 KB

TIME : About 45 seconds

Even though its not benefiting on time a lot, the backup size is very much less.

Also it is possible to take full, differential and transaction log back up using the tool. You will get a full overview/history of all the activities in the main screen about the db. A lot of other sophisticated features are there. The following link will show an article that tells a lot more about this tool.

http://www.sql-server-performance.com/da_redgate_sql_backup_spotlight.asp

Red Gate link

http://www.red-gate.com/products/SQL_Backup/index.htm

“SQL Backup is the leading tool for creating and securing your SQL Server backups. Compatible with SQL Server 2000 and 2005, it includes a range of features to make backups faster, simpler and more secure. SQL Backup is used by nearly 1,000 leading organizations such as GE, 3M, AT&T, Los Alamos National Labs, Unilever, Panasonic and the US Navy.” – Red Gate

SQL Query Analyzer Keyboard Shortcuts

2007-02-28T16:58:00.000+05:30

-- Collected By Ribu Rajan

Activity Shortcut

Bookmarks: Clear all bookmarks. CTRL-SHIFT-F2

Bookmarks: Insert or remove a bookmark (toggle). CTRL+F2

Bookmarks: Move to next bookmark. F2

Bookmarks: Move to previous bookmark. SHIFT+F2

Cancel a query. ALT+BREAK

Connections: Connect. CTRL+O

Connections: Disconnect. CTRL+F4

Connections: Disconnect and close child window. CTRL+F4

Database object information. ALT+F1

Editing: Clear the active Editor pane. CTRL+SHIFT+DEL

Editing: Comment out code. CTRL+SHIFT+C

Editing: Copy. You can also use CTRL+INSERT. CTRL+C

Editing: Cut. You can also use SHIFT+DEL. CTRL+X

Editing: Decrease indent. SHIFT+TAB

Editing: Delete through the end of a line in the Editor pane. CTRL+DEL

Editing: Find. CTRL+F

Editing: Go to a line number. CTRL+G

Editing: Increase indent. TAB

Editing: Make selection lowercase. CTRL+SHIFT+L

Editing: Make selection uppercase. CTRL+SHIFT+U

Editing: Paste. You can also use SHIFT+INSERT. CTRL+V

Editing: Remove comments. CTRL+SHIFT+R

Editing: Repeat last search or find next. F3

Editing: Replace. CTRL+H

Editing: Select all. CTRL+A

Editing: Undo. CTRL+Z

Execute a query. You can also use CTRL+E (for backward compatibility). F5

Help for SQL Query Analyzer. F1

Help for the selected Transact-SQL statement. SHIFT+F1

Navigation: Switch between query and result panes. F6

Navigation: Switch panes. Shift+F6

Navigation: Window Selector. CTRL+W

New Query window. CTRL+N

Object Browser (show/hide). F8

Object Search. F4

Parse the query and check syntax. CTRL+F5

Print. CTRL+P

Results: Display results in grid format. CTRL+D

Results: Display results in text format. CTRL+T

Results: Move the splitter. CTRL+B

Results: Save results to file. CTRL+SHIFT+F

Results: Show Results pane (toggle). CTRL+R

Save. CTRL+S

Templates: Insert a template. CTRL+SHIFT+INSERT

Templates: Replace template parameters. CTRL+SHIFT+M

Tuning: Display estimated execution plan. CTRL+L

Tuning: Display execution plan (toggle ON/OFF). CTRL+K

Tuning: Index Tuning Wizard. CTRL+I

Tuning: Show client statistics CTRL+SHIFT+S

Tuning: Show server trace. CTRL+SHIFT+T

Use database. CTRL+U

SP for Calculating EMI(Equity Monthly Installment)

2007-02-21T16:43:00.000+05:30

Create Procedure spGetEMI(

@Principle Numeric(18,2),

@DurationInMonths int,

@AnnualInterest Numeric(15,2)

)

Begin

--BineeshThomas

Declare @RateOfInterest Numeric(18,12)

Declare @Parameter Numeric(18,12)

Set @RateOfInterest=(@AnnualInterest/12)/100

Set @Parameter=power((1+@RateOfInterest),@DurationInMonths)

Select Round(@Principle*@RateOfInterest*@Parameter/(@Parameter-1),0)

End

What is wrong with 'SELECT *'?

2007-02-12T17:01:00.000+05:30

--Collected By Chacko Abraham

SELECT * is inefficient, particularly when you are only using a few of the columns in the table. This is because it actually makes TWO queries to the database: before it runs your query, it has to query the system tables to determine the name and datatypes of the columns. It is much more efficient to NAME your columns in the SQL query, and this will also help in having your column names right there... so you don't have to keep flipping back and forth between ASP page and database. In addition, this will prevent ambiguous column names in your resultset (in the case where both or all tables in the JOIN statement have columns with the same name). Further still, your code can break if it relies on ordinal position, and then someone inserts a column at the top of the table (which you can do in Enterprise Manager, or by dropping / re-creating the table). This is also a strong case for always defining columns in the INSERT list.

And finally, here's another reason to avoid SELECT * : Memo/Text columns, as well as columns containing BLOB data. Microsoft recommends to put BLOB/text columns at the end of the SELECT statement, and if there is more than one, in order of appearance in the table. This is also applicable to VARCHAR columns in SQL Server with a length greater than 255 characters.

SQL Tips- Avoid using image data types

2007-01-17T10:07:00.000+05:30

VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) will replace the TEXT, NTEXT and IMAGE data types, respectively. TEXT, NTEXT and IMAGE data types will be removed in the future version of Microsoft SQL Server. Avoid using these data types when using SQL Server 2005 and use VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.

Varbinary() can hold upto 8000 bytes

Varbinary(max) can hold almost more than about 200 MB

Taking Backup using SP

2006-11-21T17:14:00.000+05:30

--Contributed by Johnson P David. Generalized by Bineesh Thomas

Create Procedure sp_MakeBackUp(@dbName1 varchar(100), @FileName varchar(100))as

begin

DECLARE @result int

declare @backupFile varchar(100)

declare @Search varchar(100)

declare @DelFile varchar(100)

declare @PathName varchar(100)

declare @SearchPath varchar(100)

declare @CreatePath varchar(100)

declare @qry as nVarchar(1000)

set @backupFile=@FileName

set @search= 'DIR ' + @backupFile

set @DelFile='Del ' + @backupFile

if CHARINDEX('\',@backupFile,1)>2

begin

set @pathName=substring(@backupFile,1,len(@backupFile) - CHARINDEX('\',reverse(@backupFile),1))

set @SearchPath='DIR ' + @PathName

end

EXEC @result = MASTER..xp_cmdshell @SearchPath

if @result=1

begin

set @CreatePath='MD ' + @PathName

EXEC MASTER..xp_cmdshell @CreatePath

end

EXEC @result = MASTER..xp_cmdshell @search

if @result=0

begin

exec MASTER..xp_cmdshell @DelFile

end

Set @qry='BACKUP DATABASE ' +@dbName1 +' TO DISK=''' + @backupFile + ''''

--print @qry

exec(@qry)

end

To kill sleeping session from Sql server

2006-11-10T11:40:00.000+05:30

CREATE PROCEDURE KillMyJobsAS
BEGIN
CREATE TABLE #Temp(spID int,ecid int,status varchar(50),loginname varchar(100),hostname varchar(100),blk int,dbname varchar(100),cmd varchar(100))
INSERT INTO #TEMP EXEC sp_who
DECLARE @ID varchar(100)
DECLARE @STR varchar(1000)
SET @ID = ''
SET @STR = ''
DECLARE TEMPCUR CURSOR FOR
SELECT spId FROM #Temp WHERE STATUS = 'sleeping' AND dbNAME = 'My_DB' OPEN TEMPCUR
FETCH NEXT FROM TEMPCUR INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR = 'KILL ' + @ID
EXEC(@STR)
FETCH NEXT FROM TEMPCUR INTO @ID
END
CLOSE TEMPCUR
DEALLOCATE TEMPCUR
DROP TABLE #TEMP
END

Translator function for SQL Server

2006-11-08T12:06:00.000+05:30

CREATE FUNCTION TRANSLATE(@STR VARCHAR(8000),@FSTR VARCHAR(8000),@SSTR VARCHAR(8000))
RETURNS VARCHAR(4000)
AS

BEGIN
DECLARE @I INT,
@FVAL VARCHAR(1),
@SVAL VARCHAR(2)
SET @I = LEN(@FSTR)
WHILE @I > 0

BEGIN
SET @FVAL = SUBSTRING(@FSTR,@I,1)
SET @SVAL = SUBSTRING(@SSTR,@I,1)
SET @STR = REPLACE(@STR,@FVAL,@SVAL)
SET @I = @I - 1
END
RETURN @STR
END
-- Examples
--Select dbo.TRANSLATE('Bineesh','abcdefghijklmnopqrstuvwxyz','~!@#$%^&*()_+<>?-={}[]\?.')
---Will give output as !*<$${&
--Select dbo.TRANSLATE('!*<$${&','~!@#$%^&*()_+<>?-={}[]\?.','abcdefghijklmnopqrstuvwxyz')
---Again will give output back as bineesh

Padding functions for SQL SERVER

2006-11-08T11:22:00.000+05:30

-- PAD LEFT FUNCTION

CREATE FUNCTION fnPadLeft

(

@PadChar char(1),

@PadToLen int,

@BaseString varchar(100)

)

RETURNS varchar(1000)

BEGIN

DECLARE @Padded varchar(1000)

DECLARE @BaseLen int

SET @BaseLen = LEN(@BaseString)

IF @BaseLen >= @PadToLen

BEGIN

SET @Padded = @BaseString

END

ELSE

BEGIN

SET @Padded = REPLICATE(@PadChar, @PadToLen - @BaseLen) + @BaseString

END

RETURN @Padded

END

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

--################################

---PAD RIGHT FUNCTION

CREATE FUNCTION fnPadRight

(

@PadChar char(1),

@PadToLen int,

@BaseString varchar(100)

)

RETURNS varchar(1000)

BEGIN

DECLARE @Padded varchar(1000)

DECLARE @BaseLen int

SET @BaseLen = LEN(@BaseString)

IF @BaseLen >= @PadToLen

BEGIN

SET @Padded = @BaseString

END

ELSE

BEGIN

SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)

END

RETURN @Padded

END

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

--################################

--EXAMPLES

--select dbo.fnPadLeft( '-',15,125)

--select dbo.fnPadLeft( '-',20,'Hello')

Joining SP output with a table

2006-11-07T11:17:00.000+05:30

--Set the data access option for the server

EXEC sp_serveroption 'MYSERVER', 'data access', true

--GO

USE pubs

CREATE TABLE #table1( i int identity)

INSERT #table1 DEFAULT VALUES

CREATE PROC spProc1

SELECT 1 AS id

SELECT *

FROM #table1 t1 inner JOIN openquery([MYSERVER], '{call pubs..spProc1}') t2

ON t1.i = t2.id

Example of iteration in SP- Faster than a cursor

2006-11-01T12:53:00.000+05:30

Here's a quick tip that allows you to duplicate the functionality of a cursor without using one in SQL Server 2000.

declare @RowCnt int
declare @MaxRows int
declare @Email nvarchar(255)
declare @rc int

select @RowCnt = 1

declare @Import table
(
rownum int IDENTITY (1, 1) Primary key NOT NULL ,
Email nvarchar(255)
)

insert into @Import (Email) values ('blah@blah.com')
insert into @Import (Email) values ('blahblah@blah.com')

select @MaxRows=count(*) from @Import

while @RowCnt <= @MaxRows
begin

select @rc=0

select @Email = Email
from @Import
where rownum = @RowCnt

print @Email

Select @RowCnt = @RowCnt + 1

end

Alternative Fast method for finding rowcount

2006-11-01T12:49:00.001+05:30

To get the total row count in a table, we usually use the following select statement:
SELECT count(*) FROM table_name
This query performs full table scan to get the row count. You can check it by setting SET SHOWPLAN_TEXT ON for SQL Server 7.0/2000. So, if the table is very big, it can take a lot of time.

There is another way to determine the total row count in a table. You can use the sysindexes system table for this purpose. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, you can use the following select statement instead of above one:

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

There are physical read and logical read operations. A logical read occurs if the page is currently in the cache. If the page is not currently in the cache, a physical read is performed to read the page into the cache. To see how many logical or physical read operations were made, you can use SET STATISTICS IO ON command.

This is the example:

SET STATISTICS IO ON

GO

SELECT count(*) FROM tbTest

GO

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('tbTest') AND indid < 2

GO

SET STATISTICS IO OFF

GO

This is the result:

-----------

(1 row(s) affected)

Table 'tbTest'. Scan count 1, logical reads 32, physical reads 0, read-ahead reads 0.

rows

-----------

(1 row(s) affected)

Table 'sysindexes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.

13 Rules of Optimisation in TSQL

2006-11-01T12:49:00.000+05:30

1. Try to restrict the queries result set by using the WHERE clause.
This can results in good performance benefits, because SQL Server will return to client only particular rows, not all rows from the table(s). This can reduce network traffic and boost the overall performance of the query.

2. Try to restrict the queries result set by returning only the particular columns from the table, not all table's columns.
This can results in good performance benefits, because SQL Server will return to client only particular columns, not all table's columns. This can reduce network traffic and boost the overall performance of the query.

3. Use views and stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure or view name (perhaps with some parameters) instead of large heavy-duty queries text. This can be used to facilitate permission management also, because you can restrict user access to table columns they should not see.

4. Try to avoid using SQL Server cursors, whenever possible.
SQL Server cursors can result in some performance degradation in comparison with select statements. Try to use correlated subquery or derived tables, if you need to perform row-by-row operations.

5. If you need to return the total table's row count, you can use alternative way instead of SELECT COUNT(*) statement.
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2

6. Try to use constraints instead of triggers, whenever possible.
Constraints are much more efficient than triggers and can boost performance. So, you should use constraints instead of triggers, whenever possible.

7. Use table variables instead of temporary tables.
Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible. The table variables are available in SQL Server 2000 only.

8. Try to avoid the HAVING clause, whenever possible.
The HAVING clause is used to restrict the result set returned by the GROUP BY clause. When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause. This can improve the performance of your query.

9. Try to avoid using the DISTINCT clause, whenever possible.
Because using the DISTINCT clause will result in some performance degradation, you should use this clause only when it is necessary.

10. Include SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a T-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a T-SQL statement.

11. Use the select statements with TOP keyword or the SET ROWCOUNT statement, if you need to return only the first n rows.
This can improve performance of your queries, because the smaller result set will be returned. This can also reduce the traffic between the server and the clients.

12. Use the FAST number_rows table hint if you need to quickly return 'number_rows' rows.
You can quickly get the n rows and can work with them, when the query continues execution and produces its full result set.

13. Try to use UNION ALL statement instead of UNION, whenever possible.
The UNION ALL statement is much faster than UNION, because UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

Resetting seed of an identity column

2006-10-30T12:36:00.000+05:30

Deleting last row and inserting record after will make a missing number for identity columns. This case we have to reset the id column. Use the following code to reset identity column.

Declare @mxCnt as int

Select @mxCnt= max( ID_Column ) from table_name

DBCC CHECKIDENT (table_name, RESEED,@mxCnt)

SP for generating SP's , for Ins/Upd/Del

2006-10-28T11:46:00.000+05:30

--ex: sp_gensp 'absUser','Ins'
create procedure sp_gensp (@tvcTableName varchar(30), @tvcInsUpdDel varchar(3))
as

declare @text varchar(255), @col_name varchar(32), @var_name varchar(255),
@type_name varchar(13), @lnrowcount smallint, @lnnumcols smallint, @len int,
@text1 varchar(255)
set nocount on
if not @tvcInsUpdDel in ('Ins', 'Upd', 'Del')

begin
raiserror('Invalid sp type passed.', 1, 2)
return (-1)
end
select @lnrowcount = 0
create table #columns (TABLE_QUALIFIER varchar(32) NULL,
TABLE_OWNER varchar(32), TABLE_NAME varchar(32),
COLUMN_NAME varchar(32), DATA_TYPE smallint NULL,
TYPE_NAME varchar(13), PREC int,
LENGTH int, SCALE smallint NULL, RADIX smallint NULL,
NULLABLE smallint, REMARKS varchar(254) NULL,
COLUMN_DEF varchar(254) NULL, SQL_DATA_TYPE smallint,
SQL_DATETIME_SUB smallint NULL, CHAR_OCTET_LENGTH int NULL, ORDINAL_POSITION int,
IS_NULLABLE varchar(254),
SS_DATA_TYPE tinyint)
insert #columns exec sp_columns @tvcTableName
if exists(select * from #columns where TABLE_OWNER = USER)
delete #columns where TABLE_OWNER <> USER
else
delete #columns where TABLE_OWNER <> USER and TABLE_OWNER <> 'dbo'
select @text = 'IF OBJECT_ID(''' + @tvcInsUpdDel + @tvcTableName + ''') IS NOT NULL'
print @text
select @text = ' DROP PROCEDURE ' + @tvcInsUpdDel + @tvcTableName
print @text
print 'GO'
select @text = 'CREATE PROCEDURE ' + @tvcInsUpdDel + @tvcTableName
print @text
print '('
if @tvcInsUpdDel = 'Del'

begin
select @col_name = COLUMN_NAME, @type_name = TYPE_NAME
from #columns
where ORDINAL_POSITION = 1
select @var_name = '@t' + case @type_name
when 'int' then 'n'
when 'char' then 'c'
when 'varchar' then 'vc'
when 'text' then 't'
when 'datetime' then 'd'
when 'float' then 'f'
else 'u'
end
select @text = @var_name + @col_name + SPACE(1) + @type_name
print @text
print ')'
print 'AS'
select @text = 'DELETE ' + @tvcTableName + ' WHERE ' + @col_name +
' = ' + @var_name + @col_name
print @text
print 'GO'
end
if @tvcInsUpdDel in ('Ins', 'Upd')

begin
select @lnnumcols = count(COLUMN_NAME) from #columns
declare cols scroll cursor for select COLUMN_NAME, TYPE_NAME, LENGTH from #columns
open cols
fetch next from cols into @col_name, @type_name, @len
while(@@fetch_status >= 0)
begin
select @lnrowcount = @lnrowcount + 1
select @var_name = '@t' + case @type_name
when 'int' then 'n'
when 'char' then 'c'
when 'varchar' then 'vc'
when 'text' then 't'
when 'datetime' then 'd'
when 'float' then 'f'
else 'u'
end
select @text = @var_name + @col_name + SPACE(1) + @type_name +
case when @type_name in ('char', 'varchar')
then '(' + ltrim(str(@len)) + ')'
else null
end +
case when @lnrowcount = @lnnumcols then null else ',' end
print @text
fetch next from cols into @col_name, @type_name, @len
end
print ')'
print 'AS'
end
if @tvcInsUpdDel = 'Ins'

begin
select @text = 'INSERT INTO ' + @tvcTableName
print @text
print '('
-- loop for select list
select @lnrowcount = 0
fetch first from cols into @col_name, @type_name, @len
while(@@fetch_status >= 0)
begin
select @lnrowcount = @lnrowcount + 1
select @text = @col_name + case when @lnrowcount = @lnnumcols then null else ',' end
print @text
fetch next from cols into @col_name, @type_name, @len
end
print ')'
print 'VALUES'
print '('
-- loop for select list
select @lnrowcount = 0
fetch first from cols into @col_name, @type_name, @len
while(@@fetch_status >= 0)
begin
select @lnrowcount = @lnrowcount + 1
select @var_name = '@t' + case @type_name
when 'int' then 'n'
when 'char' then 'c'
when 'varchar' then 'vc'
when 'text' then 't'
when 'datetime' then 'd'
when 'float' then 'f'
else 'u'
end
select @text = @var_name + @col_name +
case when @lnrowcount = @lnnumcols then null else ',' end
print @text
fetch next from cols into @col_name, @type_name, @len
end
print ')'
print 'GO'
end
if @tvcInsUpdDel = 'Upd'

begin
select @text = 'UPDATE ' + @tvcTableName
print @text
print 'SET'
select @lnrowcount = 0
-- goto to the first column
fetch first from cols into @col_name, @type_name, @len
select @var_name = '@t' + case @type_name
when 'int' then 'n'
when 'char' then 'c'
when 'varchar' then 'vc'
when 'text' then 't'
when 'datetime' then 'd'
when 'float' then 'f'
else 'u'
end
select @text1 = 'WHERE ' + @col_name + ' = ' + @var_name + @col_name
-- start from next column
fetch next from cols into @col_name, @type_name, @len
while(@@fetch_status >= 0)
begin
select @lnrowcount = @lnrowcount + 1
select @text = @col_name
select @var_name = '@t' + case @type_name
when 'int' then 'n'
when 'char' then 'c'
when 'varchar' then 'vc'
when 'text' then 't'
when 'datetime' then 'd'
when 'float' then 'f'
else 'u'
end
select @text = @text + ' = ' + @var_name + @col_name +
case when @lnrowcount = @lnnumcols then null else ',' end
print @text
fetch next from cols into @col_name, @type_name, @len
end
print @text1
print 'GO'
end
close cols
deallocate cols
GO
GRANT EXECUTE ON dbo.sp_gensp TO public
GO

SP for changing a Primary key value having references

2006-10-27T12:22:00.000+05:30

-- Returns:0 on success, 1 if existing employee is not found, 2 if new number already exists, or > 2 if an error is encountered

/*This proc replaces an employee number*in a Master Table (Empdata), and all
*foreign tables. The employee number is*stored in a field named Emp_nbr.
*/
-- Initial Idea by T Runstein
-- Generalised By Bineesh Thomas
Create proc sp_ChangeFKValue
@OldID int, @NewID int,@PkTable Varchar(200),@PKField Varchar(200)
as
declare @TabKey int
declare @TabName sysname
declare @ColName sysname
declare @ErrTrap int
declare @lname varchar(50)
declare @fname varchar(50)
declare @mname varchar(30)
declare @dept int
declare @runStatement varchar(250)
Declare @qry as varchar(1000)
create table #TempTable
(TableKey int identity primary key not null,TableName sysname,ColName sysname null)
SET NOCOUNT ON

insert #TempTable (TableName, ColName)
(select SO.name as TableName, SS.name as ColName from sysobjects SO,
(select SC.* from syscolumns SC,
(select fkeyid, fkey from sysforeignkeys
where rkeyid =
(select id from sysobjects where name = @PkTable)) FF
where SC.id = FF.fkeyid and sc.colid = ff.fkey) SS
where SO.id = SS.id)

Set @qry='select count(*) cnt into ##cnt1 from ' + @PkTable + ' where ' + @PKField + ' = ' + ltrim(str(@OldID))
--Print @qry
exec(@qry)

set @ErrTrap =(Select top 1 cnt from ##cnt1 )
if (@ErrTrap <> 1)
return 1

Set @qry='select count(*) cnt into ##cnt2 from ' + @PkTable + ' where ' + @PKField + ' = ' + ltrim(str(@NewID))
exec(@qry)
set @ErrTrap = ( Select top 1 cnt from ##cnt2 )
if (@ErrTrap <> 0)
return 2
--Copy the information from the existing record to a new record . Check for errors before proceeding
BEGIN TRANSACTION
Set @qry='Select * into ##tmp1 from ' + @PkTable + ' Where ' + @PKField + '= ' + ltrim(str(@OldID))
exec (@qry)
Set @qry='Update ##tmp1 Set ' + @PKField + ' = ' + ltrim(str(@NewID ))
exec (@qry)
Set @qry ='Insert Into ' + @PkTable + ' Select * from ##tmp1'
exec (@qry)
set @ErrTrap = @@error
if (@ErrTrap <> 0)
begin
ROLLBACK TRANSACTION
return @ErrTrap
end
set @TabKey = 1
while not @TabKey is NULL
begin
set @TabName = (select TableName from #TempTable where TableKey = @TabKey)
set @ColName = (select ColName from #TempTable where TableKey = @TabKey)
set @runStatement = 'update [' + @TabName + '] set [' + @ColName + '] = ' + cast(@NewID as varchar(10)) + ' where [' + @ColName + '] = ' + cast(@OldID as varChar(10))
exec (@runStatement)
set @TabKey = (select min(TableKey) from #TempTable where TableKey > @TabKey)
end
Set @qry='delete '+ @PkTable +' where ' + @PKField + ' = ' + ltrim(str(@OldID))
exec(@qry)
if @@error <> 0
ROLLBACK TRANSACTION
else
COMMIT TRANSACTION
drop table #TempTable
drop table ##cnt1
drop table ##cnt2
drop table ##tmp1
return 0
set nocount off
GO

Data base information retrieval scripts

2006-10-26T17:30:00.000+05:30

-- Overview script
SELECT obj.name [Table Name], col.name [Field Name], typ.name [Type], col.length [Length]
, CASE col.isnullable WHEN '0' THEN 'No' WHEN 1 THEN 'Yes' END [Nullable]
, const.[Refer to]
FROM dbo.sysobjects obj
INNER JOIN dbo.syscolumns col ON obj.id = col.id
AND obj.xtype = 'U'
INNER JOIN dbo.systypes typ ON col.xtype = typ.xtype
LEFT JOIN (
SELECT frk.constid [ConstraintID]
, frk.fkeyid [TableID], frk.fkey[ColumnID]
, colref.name + ' of ' + objref.name [Refer to]
, frk.rkeyid [TableID refered], frk.rkey[ColumnID refered]
FROM dbo.sysforeignkeys frk
INNER JOIN dbo.syscolumns colref on frk.rkeyid = colref.id
AND frk.rkey = colref.colid
INNER JOIN dbo.sysobjects objref on colref.id = objref.id
) const on col.colid = const.ColumnID
AND col.id = const.TableId
ORDER BY [Table Name]

-- Info about Tables
select obj.id [Table ID], obj.name [Table Name]
from dbo.sysobjects obj
where obj.xtype = 'U'

-- Info about Fields of Tables
select col.id [TableID], col.colid [ColumnID], col.name [Field Name], typ.name [Type], col.length[Length]
, case col.isnullable when '0' then 'Yes' when 1 then 'No' end [Nullable]
, const.[Refer to]
from dbo.syscolumns col
inner join dbo.systypes typ on col.xtype = typ.xtype
inner join dbo.sysobjects obj on col.id = obj.id
and obj.xtype = 'U'
left join (
select frk.constid [ConstraintID]
, frk.fkeyid [TableID], frk.fkey[ColumnID]
, colref.name + ' of ' + objref.name [Refer to]
, frk.rkeyid [TableID refered], frk.rkey[ColumnID refered]
from dbo.sysforeignkeys frk
inner join dbo.syscolumns colref on frk.rkeyid = colref.id
and frk.rkey = colref.colid
inner join dbo.sysobjects objref on colref.id = objref.id
) const on col.colid = const.ColumnID
and col.id = const.TableId

-- Info about Database Server
--USE master
--select dbs.name [Database], cast (dbs.crdate as smalldatetime) [Created Date], dbs.filename [Path]
--from dbo.sysdatabases dbs

-- List of DataType
select typ.name [Data Type], typ.length [Length]
from dbo.systypes typ

-- List all of Constraint
select frk.constid [ConstraintID]
, frk.fkeyid [TableID], frk.fkey[ColumnID]
, frk.rkeyid [TableID refered], frk.rkey[ColumnID refered]
from dbo.sysforeignkeys frk

select frk.constid [ConstraintID]
, obj.name [Table], col.name [Column]
, frk.fkeyid [TableID], frk.fkey[ColumnID]
, objref.name [Table refered], colref.name [Column refered]
, frk.rkeyid [TableID refered], frk.rkey[ColumnID refered]
from dbo.sysforeignkeys frk
inner join dbo.syscolumns colref on frk.rkeyid = colref.id
and frk.rkey = colref.colid
inner join dbo.sysobjects objref on colref.id = objref.id
inner join dbo.syscolumns col on frk.fkeyid = col.id
and frk.fkey = col.colid
inner join dbo.sysobjects obj on col.id = obj.id

Clear Doubts - Technical: SQL Server Full-Text Indexing

2006-10-25T16:37:00.000+05:30

Clear Doubts - Technical: SQL Server Full-Text Indexing

Clear Doubts - Technical: SQL Server Tips - Updating Values in SysObjects

2006-10-25T16:36:00.002+05:30

Clear Doubts - Technical: SQL Server Tips - Updating Values in SysObjects

Clear Doubts - Technical: Sql Server Tips- making command for 'SELECT' statement in Sql Query analyser

2006-10-25T16:36:00.001+05:30

Clear Doubts - Technical: Sql Server Tips- making command for 'SELECT' statement in Sql Query analyser

Clear Doubts - Technical: SQL Server Tips-Sending SMPT mail (with attachment )directly using stored procedure

2006-10-25T16:36:00.000+05:30

Clear Doubts - Technical: SQL Server Tips-Sending SMPT mail (with attachment )directly using stored procedure

Clear Doubts - Technical: SQL Server Tips - User database marked suspect

2006-10-25T16:34:00.000+05:30

Clear Doubts - Technical: SQL Server Tips - User database marked suspect

A to Z on SQL Server

2006-10-25T16:30:00.000+05:30

This will provide a stage to discuss all abt microsoft sqlserver