Important and useful command in SQL server 2005/2008

How to get all trigger definitions in database?

            SELECT   sysobjects.name AS [Trigger Name],

            SUBSTRING(syscomments.text, 0, 1000) AS [Trigger Definition],

            OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],

            syscomments.encrypted AS [IsEncrpted] FROM sysobjects 

            INNER JOIN syscomments ON sysobjects.id = syscomments.id WHERE   

            (sysobjects.xtype = 'TR')

add user to sql server admin role 

sp_addsrvrolemember 'domain\username', 'sysadmin'

Count all system objects

select count(*) as [Table] from sys.objects where type ='U'

select count(*) as [Function] from sys.objects where type ='FN'

select count(*) as [View] from sys.objects where type ='V'

select count(*) as [SP] from sys.objects where type ='P'

select count(*) as [Triggers] from sys.objects where type ='TR'

select count(*) as [Contraints] from sys.objects where type ='PK'

list all tables referred by a stored procedure

SELECT o.name, t.TABLE_NAME, c.text
  FROM syscomments c
  JOIN sysobjects o
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%'+t.TABLE_NAME+'%' 

list all triggers in database

SELECT 

    [Table] = OBJECT_NAME(o.parent_obj), 

    [Trigger] = o.[name], 

    [Type] = CASE WHEN 

        ( 

        SELECT 

            cmptlevel 

        FROM 

            master.dbo.sysdatabases 

        WHERE 

            [name] = DB_NAME() 

        ) = 80 THEN 

        CASE WHEN 

            OBJECTPROPERTY(o.[id], 

            'ExecIsInsteadOfTrigger') = 1 THEN 

                'Instead Of' 

            ELSE 

                'After' 

            END 

        ELSE 

            'After' 

        END, 

    [Insert] = CASE WHEN 

        OBJECTPROPERTY(o.[id], 

        'ExecIsInsertTrigger') = 1 THEN 

            'Yes' 

        ELSE 

            'No' 

        END, 

    [Update] = CASE WHEN 

        OBJECTPROPERTY(o.[id], 

        'ExecIsUpdateTrigger') = 1 THEN 

            'Yes' 

        ELSE 

            'No' 

        END, 

    [Delete] = CASE WHEN  

        OBJECTPROPERTY(o.[id], 

        'ExecIsDeleteTrigger') = 1 THEN 

            'Yes' 

        ELSE 

            'No' 

        END, 

    [Enabled?] = CASE WHEN 

        OBJECTPROPERTY(o.[id], 

        'ExecIsTriggerDisabled') = 0 THEN 

            'Enabled' 

        ELSE 

            'Disabled' 

        END 

FROM 

    sysobjects o 

WHERE 

    OBJECTPROPERTY(o.[id], 'IsTrigger') = 1 

    -- leave out the following clause if you need to 

    -- include system triggers, e.g. those in MSDB 

    AND 

    OBJECTPROPERTY(o.[id], 'IsMSShipped') = 0 

ORDER BY 

    1,2

Sql Server: Convert table Column data into comma separated string or row

SQL SERVER – Create a Comma Delimited/Seprated string or row Using SELECT Clause From Table Column/fields

Here  I am using COALESCE function Sql Server 2005/2008 to convert a field into comma separated value of string or row
My data in the table looks like :
 

The result I want to show is like :




Select query will be:

Assuming my table name is  #test, It has 2 column field1,field2

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ',' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1

If you wish select all the rows of field2 as comma separated list then query will be:


SELECT STUFF( -- Remove first comma
    (
        SELECT  ', ' + field2 FROM -- create comma separated values
        (
          SELECT field2 FROM #test --Your query here
        ) AS T FOR XML PATH('')
    )
    ,1,1,'') AS field2

OR

DECLARE @test NVARCHAR(max)  
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test
SELECT field2= @test 


Output:
 

Upload file without Postbacking AJAX in ASP.net

This article will help you to upload file asynchronously  in asp.net

download the full code from here

I am using a iframe a bit trick to upload the file asynchronously with AJAX without any postback to the server.

hope the code will help you.

Asp.net:Multiple insertion of record into a SQL Server database table using XML

Every time we face problem bulk insertion through store procedure to perform the following task

1)      Multiple insert in table

2)      Select multiple rows using  in-clause at where condition

3)      Delete multiple rows
      Save data from grid to database table in sql server 2005/2008

Here I am writing some very good code to pass multiple value in store procedure using xml string which is much faster and we can pass a large value having multiple rows and column some time using Gridview and other similar control.

This is the best and optimize method to insert multiple records in one go from asp.net to database table in sql server 2005/2008 using the XML formatting.

Convert  Datatable/Dataset to XML string

public string DataTableToXML(DataTable table)

{

StringWriter xml = new StringWriter();

string xmldata = string.Empty;

table.TableName = "journal";

table.WriteXml(xml,XmlWriteMode.IgnoreSchema);

xmldata = xml.ToString();

return xmldata;

}

the out put XML of the above code will be as given in the store-procedure variable.

Handling of XML string inside Store procedure/Query

Pass this xml string in your store procedure and handle this string in your SP by as following code given

Create procedure handlexml(@journalstring varchar(1000))
As
Begin


/*declare @journalstring varchar(2000)='<DocumentElement>
  <journal>
    <jrnlfk>95</jrnlfk>
    <orderfk>18</orderfk>
  </journal>
  <journal>
    <jrnlfk>165</jrnlfk>
    <orderfk>2</orderfk>
  </journal>
  <journal>
    <jrnlfk>115</jrnlfk>
    <orderfk>2</orderfk>
  </journal>
</DocumentElement>' */

Suppose your string is xml element/Node and coming as above format

------------------------------------------

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT,

@journalstring

---------------------------------------------For XML Element--------------------------

SELECT jrnlfk,orderfk

FROM OPENXML(@hdoc, 'DocumentElement/journal',3)

WITH (jrnlfk int,

orderfk int)

-----------------------------------For XML Attribute--------------------------

declare @journalstring varchar(2000)='<Journal>
<remit journalfk="165" orderno="25"/>
<remit journalfk="197" orderno="15"/>
</Journal>'

---------------------------------------------------XXX-----------------------

SELECT *

FROM OPENXML(@hdoc, 'Journal/remit', 2)

WITH (journal int '@journalfk',

orderno int '@orderno')

end -- end of procedure

Hope this help you

Happy Programming