Create triggers Dynamically on Database

Create triggers Dynamically on Database

create Proc CreateTriggers
as
BEGIN
SET nocount ON

DECLARE @t TABLE
  (
     NAME VARCHAR(50)
  )

EXEC DropTrigger
/*

CREATE PROC DropTrigger
AS
  BEGIN
      SET nocount ON

      DECLARE @t TABLE
        (
           NAME VARCHAR(50)
        )

      INSERT INTO @t
      SELECT DISTINCT table_name
      FROM   information_schema.COLUMNS

      DECLARE @TableName VARCHAR(50)=''
      DECLARE cur CURSOR FOR
        SELECT NAME
        FROM   @t

      OPEN cur

      FETCH next FROM cur INTO @TableName

      WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @DROPTrigger VARCHAR(max)=''
            DECLARE @DROPTABLE VARCHAR(max)=''
            DECLARE @CREATELOGTBL VARCHAR(max)=''
            DECLARE @TRIGGERCREATE VARCHAR(max)=''
            DECLARE @STR VARCHAR(max)=''

            SELECT @STR = @STR + ', ' ++ '[' + column_name + ']'
            FROM   information_schema.COLUMNS
            WHERE  table_name = @TableName

            DECLARE @DropTRG VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
              + ''') is not null' + Char(10) + 'begin' + Char(10)
              + 'drop trigger trg' + @tableName + ';' + Char(10)
              + 'end'

            SET @DROPTrigger= @DropTRG

            --EXEC(@DROPTrigger)
            DECLARE @CREATE VARCHAR(100)='CREATE TABLE trg' + @tableName +
                                         '_History('
            DECLARE @Drop VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
              + '_History'') is not null' + Char(10) + 'begin'
              + Char(10) + 'drop table trg' + @tableName
              + '_History' + Char(10) + 'end'

            SET @DROPTABLE= @drop

            EXEC(@DROPTrigger)

            EXEC(@DROPTABLE)

            FETCH next FROM cur INTO @TableName
        END

      CLOSE cur

      DEALLOCATE cur

      SET nocount OFF
  END

*/
INSERT INTO @t
SELECT DISTINCT table_name
FROM   information_schema.COLUMNS

DECLARE @TableName VARCHAR(50)=''
DECLARE cur CURSOR FOR
  SELECT NAME
  FROM   @t

OPEN cur

FETCH next FROM cur INTO @TableName

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @DROPTrigger VARCHAR(max)=''
      DECLARE @DROPTABLE VARCHAR(max)=''
      DECLARE @CREATELOGTBL VARCHAR(max)=''
      DECLARE @TRIGGERCREATE VARCHAR(max)=''
      DECLARE @STR VARCHAR(max)=''

      SELECT @STR = @STR + ', ' ++ '[' + column_name + ']'
      FROM   information_schema.COLUMNS
      WHERE  table_name = @TableName

      DECLARE @DropTRG VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
        + ''') is not null' + Char(10) + 'begin' + Char(10)
        + 'drop trigger trg' + @tableName + ';' + Char(10)
        + 'end'

      SET @DROPTrigger= @DropTRG

      --EXEC(@DROPTrigger)
      DECLARE @CREATE VARCHAR(100)='CREATE TABLE trg' + @tableName + '_History('
      DECLARE @Drop VARCHAR(max)= 'if OBJECT_ID(''trg' + @tableName
        + '_History'') is not null' + Char(10) + 'begin'
        + Char(10) + 'drop table trg' + @tableName
        + '_History' + Char(10) + 'end'

      SET @DROPTABLE= @drop

      --EXEC(@DROPTABLE)
      DECLARE @STRCRE VARCHAR(max)=''

      SELECT @STRCRE = @STRCRE + ', ' + CASE WHEN character_maximum_length IS
                       NULL
                       THEN '['+
                              column_name
                              +']'+' '+data_type ELSE '['+column_name+']'+' '+
                       data_type+'('+
                              CONVERT(VARCHAR(10), Replace(
                       character_maximum_length,
                       '-1',
                              'MAX'))+')' END
      FROM   information_schema.COLUMNS
      WHERE  table_name = @TableName

      SET @CREATELOGTBL= @CREATE + ' '
                         + RIGHT(@STRCRE, Len(@STRCRE)-1)
                         + ',Actions varchar(20),CreatedDate1 dateTime)'
                         + Char(10)
      --EXEC (@CREATELOGTBL)
      SET @TRIGGERCREATE= 'create trigger trg' + @tableName + ' on ' + '['
                          + @tableName + ']' + Char(10)
                          + ' for insert,update , delete ' + Char(10)
                          + 'AS ' + Char(10) + 'BEGIN' + Char(10)
                          + 'IF EXISTS(SELECT * FROM inserted)'
                          + Char(10) + 'BEGIN' + Char(10) + 'INSERT INTO trg'
                          + @tableName + '_History' + '('
                          + RIGHT(@STR, Len(@str)-1)
                          + ',Actions, CreatedDate1' + ')'
                          + ' SELECT *,''INSERTED'',getdate() FROM inserted'
                          + Char(10) + 'END' + Char(10)
                          + 'IF EXISTS(SELECT * FROM deleted)'
                          + Char(10) + 'BEGIN' + Char(10) + 'INSERT INTO trg'
                          + @tableName + '_History' + '('
                          + RIGHT(@STR, Len(@str)-1)
                          + ',Actions, CreatedDate1' + ')'
                          + ' SELECT *,''DELETED'',getdate() FROM deleted'
                          + Char(10) + 'END' + Char(10) + 'END'

      EXEC(@DROPTrigger)

      EXEC(@DROPTABLE)

      EXEC (@CREATELOGTBL)

      EXEC (@TRIGGERCREATE)

      FETCH next FROM cur INTO @TableName
  END

CLOSE cur

DEALLOCATE cur

SET nocount OFF

end

sp_Send_dbmail in SQL Server

How to send Image with sp_send_dbmail in SQL SERVER

create trigger trgJobSeeker
on JobSeeker
for insert
as
begin

if object_id('tempdb..#JobSeeker') is not null
begin
drop table #JobSeeker
end

declare @Email varchar(100),@name varchar(50)
declare @Body varchar(max),@tableHTML varchar(max),@tableHTML1 varchar(max)

    select substring(email,1,charindex('@',email)-1) as Name,EMail into #JobSeeker from inserted

select @Email=email, @name=Name from #JobSeeker

select @Body=Name from #JobSeeker

/*
 To Admin
  */
SET @tableHTML =
N'   <img src="https://lh3.googleusercontent.com/-gMAaKtKOWyw/T0szCDAN-iI/AAAAAAAACP0/laJwA32vGus/w689-h685-no/suri.jpg" alt="" align="right" />
<div style="clear: right">'+N'

                          <p > Catherine Wehage,  </p>
 <p >  </p>

 <p style=" text-indent: 5em;">'+ @name+' has expressed interest online about a Volunteer position with me.</p>
 <p >  </p>
<p > If there are any positions open:</p>
<p style=" text-indent: 5em;"> 1. </p>
<p style=" text-indent: 5em;"> 2. </p>
<p style=" text-indent: 5em;"> 3. </p>
<p style=" text-indent: 5em;"> 4.  procedure</p>
<p >  </p>
<p >Not anything:</p>
<p style=" text-indent: 5em;"> 1. Inform them </p>
<p style=" text-indent: 5em;"> 2. Ask to keep name </p>
<p style=" text-indent: 5em;"> 3. Inform them o</p>
<p> </p>
<p>Thanks,</p>

<p>'+@Body+'</p>

 </p> '

 /* to Candidate */

 set @tableHTML1=N'   <img src="https://lh3.googleusercontent.com/-gMAaKtKOWyw/T0szCDAN-iI/AAAAAAAACP0/laJwA32vGus/w689-h685-no/suri.jpg" alt="" align="right" />
<div style="clear: right">'+N'

                          <p > Dear '+@Body+',   </p>
 <p >  </p>

 <p style=" text-indent: 5em;">Thank you for your interest --- team! </p>
 <p >  </p>
<p > abcdefghij. </p>

<p >  </p>
<p >Enter your test here!</p>
<p> </p>
<p>Sincerely, </p>
<p> </p>

<p>Vagalla Suresh</p>
<p> Author</p>

 </p> '

 /*
 To Admin
  */
exec msdb.dbo.sp_send_dbmail
          @profile_name='DLM-Mail',
          @recipients='vagalla.suresh@gmail.com',
          @subject='New Candidate Registered successfully',
          @body=@tableHTML,
 @body_format = 'HTML'

/* to Candidate */
exec msdb.dbo.sp_send_dbmail
          @profile_name='DLM-Mail',
          @recipients='vagalla.suresh@gmail.com',
          @subject='Registered  successfully',
          @body=@tableHTML1,
 @body_format = 'HTML'

end

--truncate table JobSeeker
--insert into JobSeeker(Email,IsActive) values('vagalla.suresh@gmail.com',1)

For table format

USE [DB]
GO
/****** Object:  Trigger [dbo].[trgStudentinsert]    Script Date: 04-09-2014 11:02:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgStudentinsert]
ON [dbo].[Student]
after UPDATE
AS
    SET nocount ON
    DECLARE @email  VARCHAR(255),
            @ordnbr VARCHAR(15),
            @sql    VARCHAR(max)
    DECLARE @xml NVARCHAR(max)
    DECLARE @body NVARCHAR(max)
    DECLARE @ParentName VARCHAR(50)
    DECLARE @Slot VARCHAR(5000)
    IF UPDATE(interviewhistoryid)
      BEGIN
     
          IF Object_id('tempdb..#TempStudent') IS NOT NULL
            BEGIN
                DROP TABLE #tempstudent
            END
          IF Object_id('tempdb..#temp123') IS NOT NULL
            BEGIN
                DROP TABLE #temp123
            END
          IF EXISTS(SELECT 1
                    FROM   inserted I
                           JOIN deleted D
                             ON I.studentid = D.studentid
                                AND I.interviewhistoryid <>
                   D.interviewhistoryid)
            BEGIN
                SELECT b. *
                INTO   #tempstudent
                FROM   student a
                       INNER JOIN inserted b
                               ON a.studentid = b.studentid
                SELECT p.title + ' ' + P.firstname + ' '
                       + Isnull(p.lastname, '')                   AS ParentName,
                       p.email                                    AS ParentEmail
                       ,
s.firstname + ' ' + Isnull(s.lastname, '') AS StudentName,
s.email                                    AS StudentEmail,
'  '
+ CONVERT(VARCHAR(20), Format (ih.date, 'MMM dd yyyy'))
+ ' between ' + ivs.scheduletime           AS ScheduledOn
INTO   #temp123
FROM   #tempstudent S
INNER JOIN interviewhistory IH
        ON s.interviewhistoryid = IH.interviewhistoryid
INNER JOIN interviewslot IVS
        ON IVS.scheduleid = IH.scheduleid
INNER JOIN parent P
        ON p.studentid = s.studentid
DECLARE @tableHTML NVARCHAR(max);
DECLARE @Parent      VARCHAR(50),
 @Student     VARCHAR(50),
 @ScheduledOn VARCHAR(max)
SELECT @Parent = parentname,
@Student = studentname,
@ScheduledOn = scheduledon
FROM   #temp123
SELECT @email = ParentEmail+';' + studentemail
       
FROM   #temp123
SET @tableHTML =
N'                    
                  <head>
                          <STYLE TYPE="text/css">

                  p.title{font-family:arial;font-size:12pt;color:RED;font:bold}
                  th{font-family:arial;font-size:10pt;color:white;background-color:blue;text-align:Centre;padding-left:5px;padding-right:5px}
                  .data{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:left;padding-left:5px;padding-right:5px}
                  .datara{font-family:arial;font-size:8pt;color:black;background-color:white;border:1px solid black;text-align:right;padding-left:5px;padding-right:5px}
                  </STYLE>
                  </head>
<img src="https://lh3.googleusercontent.com/-gMAaKtKOWyw/T0szCDAN-iI/AAAAAAAACP0/laJwA32vGus/w689-h685-no/suri.jpg" alt="" align="right" />
<div style="clear: right">
                          <p > Hello '
          + @Parent
          + ', </p>
 <p >  </p>
 <p > Thank you for applying your child '
          + @Student
          +
' to vagalla's  Program. We look forward to meeting you and your family at the online on '
+ @ScheduledOn
+ '</p>
 <p > In the meantime, if you have any questions, please contact me Basketball, vagalla@mail.com or---, vagalla@program.org. </p>
<p class="title">  </p>
<p class="title"> Interview Schedule </p>
                        <table>
<table border = 1>
                        <tr>
                              <th>ParentName</th>
                              <th>ParentEmail</th>
                              <th>StudentName</th>
                              <th>StudentEmail</th>
                              <th>ScheduledOn</th>
                        </tr>'
+ Cast ( ( SELECT [td/@class]='data', td = parentname, "*"='',
[td/@class]='data', td = parentemail, "*"='', [td/@class]=
'data', td = studentname, "*"='', [td/@class]='data', td =
studentemail, "*"='', [td/@class]='data', td = scheduledon FROM
#temp123 FOR xml path('tr'), type ) AS NVARCHAR(max) )
+ N'</table>
<p > Thanks, </p> <p > Vagalla Team</p> </div>'
EXEC msdb.dbo.Sp_send_dbmail
@Profile_name='DLM-Mail',
@recipients=@email,
@subject = 'Slot Booked for the Interview',
@body = @tableHTML,
@body_format = 'HTML'
END
END

How to find KeyConstraint Columns on Table in SQL Server

Find PrimaryKey Columns on Table

 here i want to know the Column Name on which the key constraints are created.

Initially i was concentrated on Primary Key constraints.

Here i am displaying Constraint Name ,Tablename ,ColumnName

SELECT i.name                               AS IndexName,
       Object_name(ic.object_id)            AS TableName,
       Col_name(ic.object_id, ic.column_id) AS ColumnName
FROM   sys.indexes AS i
       INNER JOIN sys.index_columns AS ic
               ON i.object_id = ic.object_id
                  AND i.index_id = ic.index_id
WHERE  i.is_primary_key = 1

SELECT i.name                               AS IndexName,
       Object_name(ic.object_id)            AS TableName,
       Col_name(ic.object_id, ic.column_id) AS ColumnName
FROM   sys.indexes AS i
       INNER JOIN sys.index_columns AS ic
               ON i.object_id = ic.object_id
                  AND i.index_id = ic.index_id

If you want the table name only, use the CTE & to know the primarykey of a specific table you can filter it with table name

with cte as(
SELECT i.name AS IndexName,
        OBJECT_NAME(ic.OBJECT_ID) AS TableName,
        COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM    sys.indexes AS i INNER JOIN  sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID
                                AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1
)select *  from cte
--where TableName='Document'

How to TRUNCATE Data From All TABLES of a DataBase in SQL Server

TRUNCATE Data From All TABLES of a DataBase in SQL Server

Here the following script help full to you to truncate data from all tables in a database

USE dbname
go
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
go
EXEC sp_MSforeachtable 'DELETE FROM ?'
go
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
go
EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'
go

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

This is for for disable all the constraints in database

EXEC sp_MSforeachtable 'DELETE FROM ?'

Here we can delete all the data from tables

EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT

 Again we need to enable the constraints

Now,

 EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'

By using DBCC command reset the identity

All process comes under truncation of a database 

The purpose of reseed is to re-set the identity value ,because truncate will automatically resets, but delete cannot. So it is mandatory to use the DBCC command for reset Identity value 

Display State Under City in SQL Server

How to Display State Under City in SQL Server

In my DB i have Two tables like State and City. Here State id is referenced

I need to display the data First Metro City and next StateName under that City name in Alphabetical order.

For finding is metro city or not i taken a flag named IsMetrocity

Here is the solution:

select CityName,StateId from City where IsMertoCity=1
union all
SELECT sc.name,sc.StateId FROM  state AS s2
LEFT JOIN (SELECT s.StateNmae AS name, s.stateid,0 as IsMertoCity FROM   state s
            UNION ALL
            SELECT c.cityname AS name, c.StateId,IsMertoCity FROM   city c ) AS sc
ON sc.stateid = s2.stateid

Here first 4 are the metroCities and Starting StateId 1 is StateName and remaining Cities,next starting stateid 2 is StateName remaining are Cities under the State and so on.

Job Scheduling in SQL SERVER

JOB SCHEDULING

Step 1:  Go to SQL Server Agent-->Click on jobs-->NEW JOB

Step 2: Provide the name and Database owner
At the description you can write like it could give brief introduction about the job

Step 3: Select the steps-->Click on new

Step 4: Provide the step name
Type is a T-SQL (from drop down list you can choose different types)
Database is your database Name
Write the command what we have to schedule daily
(My self here i am taking the backup of one of my data bases) Click OK

Below snapshot show after the Step finished

Step 5: For scheduling click NEW

Step 6: Give the suitable name
Schedule type, Here from the drop down we have different types, According to type of schedule select one

Click OK

Find your JOB name here(If job created successfully)

In the error logs you can find the summery of your job

Detach and Attach in SQL SERVER

Detach and Attach

Step 1: Attach and Detach database
Before proceeding need to know what is attach and detach!
While we took backup of a database, the .bak file saved in drive and also exists in server
when ever use a detach option this will takes backup of database and drops the database from server
While attach like a restore of a database which already backedup.

The following are the steps how we can attach and detach the database

Step 2: Click on database -->Tasks-->Detach

If you found following error, Set your database to Single User mode using SET operator

Now choose drop connections then click OK

Also the following is the SP also how to set database into single user mode

Attach:
Click on database-->Attach

Choose database which you want to restore

click ok

The database came to previous position

SQL SERVER 2014 Installation

SQL SERVER 2014 Installation

Step 1: Click on your set up file 

Step 2: Click on installation then select first one ( New SQL Server standard-alone installation or .... )

Step 3: Specify a free edition (it could be free for 180 days)
If you have a product key then choose enter product key

Step 4: Check the BOX accept the licence terms then click next

Step 5: After finished the Updates Checking click next

Step 6:If no errors you can click next

Step 7: Choose one from the following based on which type you need. All about headings given the features in description

Step 8: Select instance features and/or Shared features according to your work requirement click next

Step 9: Give the instance name may be default or Named

Step 10:  Choose the Service account Name and startup Type

Step 11: Server  configuration -->Authentication Mode
Here Mixed mode we can keep security for log-in. Windows authentication Mode required no security but another must--> Specify SQL Server administrator -->NEXT

and this is for Analysis Services

Step 12: This is about the reporting services choose install only or install and configure

This could be the final step

Import Data from EXCEL

How to Import Data from EXCEL to Destination

Import data from excel through wizard 

Step 1: Choose your database where to import data -->Tasks-->Import Data

Step 2: Click Next

Step 3: Choose the Data source as a Microsoft Excel and browse the file path as well as choose the Excel Version click next

Here we have the options to get the columns Dynamically (like First row has column names) or Manually we have to provide

Step 4:Now choose our destination and Authentication type also database -->click Next

Step 5: Choose either option click next

Step 6: Select the sheet where you have a required data then click next

Step 7:Here we have the options to choose either Package or run immediately 

Run immediately is a process which retrieves the data instantly 

If it is a package we can run package along with configuration file at any environment 

Step 8: Close