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.
|
4 DBA, Databases, & Data Code Snippets
Group: DBA, Databases, & Data
Topic: ANSI SQL Scripting
|
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
|
Topic: Microsoft SQL Server
|
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
|
|
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
|
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
|
|
|