I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

June Edition
Subscribe now! It's Free!
Enter your email:

   ► KBDBA Knowledge Base  Print This    Code Snippet DB All Groups  

DBA Code Snippets Page

These Code Snippets are contributed by you (our online community members). They are organized by our knowledge base topics. Specifically, by the DBA sub-topics.

Contribute a Code Snippet
Expand All

4 DBA, Databases, & Data Code Snippets

Group: DBA, Databases, & Data


Topic: ANSI SQL Scripting

-Collapse +Expand 1. Not In SQL Select Query
 

The following selects all the records in Table1 where IDField is not in Table2.

SELECT Table1.*
FROM Table1
LEFT JOIN Table2 ON Table1.IDField = Table2.IDField
WHERE Table2.IDField is null
Posted By Mike Prestwood, Post #101797, KB Topic: ANSI SQL Scripting



Topic: Microsoft SQL Server

-Collapse +Expand 2. Easy SQL Server Backup Script
 

Learn how to make an easy SQL Server Script that will automatically back up all your databases in a simple way.

-- Back Up All Databases
-- by Bryan Valencia

--create temp table
declare @temp table(commands varchar(500), completed bit)

--load it with backup commands
insert into @temp (commands, completed)
(select
    'BACKUP DATABASE ['+name+
    '] TO  DISK = N''J:\Backups\'+name+
    '.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'''+name+
    '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10',
    0
from
    master.sys.databases
where
    owner_sid <> 0x01 and state_desc='ONLINE'
)

--variable for the current command
declare @thisCommand varchar(500);

--loop through the table
while (select count(1) from @temp where completed=0)>0
begin
    --find the first row that has not already been executed
    select top 1 @thisCommand = commands from @temp where completed=0

    --show the command in the "mesage" output window.
    print @thisCommand

    --execute the command
    EXEC (@thisCommand);

    --flag this row as completed.
    update @temp set completed=1 where commands=@thisCommand
end

--show the user the rows that have been found.
select * from @temp

Posted By Bryan Valencia, Post #102439, KB Topic: Microsoft SQL Server
-Collapse +Expand 3. Move Table to Another Schema
 

In MS-SQL, to move an object such as a table or view from one schema to another, use alter schema.

alter schema [ToSchema] transfer FromSchema.[Object] 

 

For example, the following moves the Orders table from the user1 schema t the dbo schema.

alter schema [dbo] transfer user1.[Orders] 
Posted By Mike Prestwood, Post #102065, KB Topic: Microsoft SQL Server



Topic: MS SQL 2005

-Collapse +Expand 4. MSSQL Update Trigger Example
 

This tutorial shows how you would create a trigger in Microsoft SQL Server 2005/2008 that will date/timestamp a column named last_updated everytime any data in the row is updated.

This example assumes a primary key that includes 3 fields.

CREATE TRIGGER MyTableUpdate
ON dbo.MyTable
FOR update
AS
UPDATE
MyTable
SET last_updated = GetDate()
From MyTable Inner Join Inserted On
MyTable.KeyField1 = Inserted.KeyField1
and MyTable.KeyField2 = Inserted.KeyField2
and MyTable.KeyField3 = Inserted.KeyField3
Posted By Bryan Valencia, Post #100987, KB Topic: MS SQL 2005
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech