TOP 5 Sql Server Scripts

/* 1 -------------------------------Script------------------------------

This script captures CPU Utilization Percent using TSQL. It uses system functions @@CPU_BUSY and @@IDLE

to see what is the CPU Utilization % at a particular instant. You can then use this number in a table or variable

to trigger other events.

@@CPU_BUSY  shows the time SQL Server has been busy

@@IDLE shows the time SQL Server has been idle

--------------------------------------------------------------------*/

DECLARE @CPU_BUSY int

DECLARE @IDLE int

SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE

WAITFOR DELAY '000:00:01'

SELECT

(@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) * 1.00) *100 AS CPU_Utilization_Pct

/* 2 ----------------------------Script------------------------------

sp_change_users_login is used to map an existing database user to a SQL Server login. The typical use is when you restore a database to a new server, the database users can be orphaned.

The syntax is as follows

sp_change_users_login [ @Action = ] 'action'

    [ , [ @UserNamePattern = ] 'user' ]

    [ , [ @LoginName = ] 'login' ]

        [ , [ @Password = ] 'password' ]

According to MSDN this feature maybe deprecated in a future release

---------------------------------------------------------------*/

--Here we are mapping the user WebUser to login WebLogin

EXEC sp_change_users_login 'Update_One', 'WebUser', 'WebLogin'

/* 3 -------------------------------Script-----------------------------

Sometimes it is necessary to change properties of all your databases in one shot

In this script, we use a system table to get all the database names on our server. Next

we use a cursor to loop through all the records and then change the recovery model using

Alter Database command

---------------------------------------------------------------*/

--declaration of variables

declare

@dbnm sysname,

@sql varchar(100)

-- begin cursor to get the database names

declare cursor_db cursor

for select name from sys.databases where name != 'tempdb'

-- using a cursor to loop through database names and change recovery model

open cursor_db

fetch next from cursor_db into @dbnm

while @@fetch_status = 0

      begin 

      --print 'database is ' + @dbnm

      set @sql='alter database ' + @dbnm + ' set recovery simple'

      print 'sql is ' + @sql

      exec (@sql)

            fetch next from cursor_db into @dbnm

      end

close cursor_db

deallocate  cursor_db

/* 4 --------------------------------Script---------------------------------

In this script we cover Basic DML statement including SELECT, INSERT, UPDATE AND DELETE

SELECT statement returns the data from tables. Does not make any changes to the underlying data. It is the most

commonly used SQL statement. This statement can use a sub-select (sub query). Here is the syntax

SELECT fields FROM tables WHERE condition ORDERBY fields

The INSERT statement adds one or more new rows to a table. In a simplified treatment, INSERT has this form:

INSERT [INTO] table_or_view [(column_list)] data_values

The UPDATE is used to update data in tables. Here is the syntax

UPDATE TABLE SET COLUMN WHERE CONDITION

DELETE statement removes rows from a table! Be very careful with this statement. Here is the syntax

DELETE FROM TABLE WHERE CONDITION

More information can be found in this document, SQL Addons.doc

These example utilizie SQL Server sample database ADVENTUREWORKS

------------------------------------------------------------------*/

USE ADVENTUREWORKS

--SELECT

--select all data

SELECT *

FROM [AdventureWorks].[Person].[Address]

 

 

--select specific columns

 

SELECT

[AddressID]

,[AddressLine1]

,[AddressLine2]

,[City]

,[StateProvinceID]

,[PostalCode]

FROM [AdventureWorks].[Person].[Address] 

--select specific rows

SELECT *

FROM [AdventureWorks].[Person].[Address]

WHERE [City]='Seattle'

--INSERT

--inserting a row into Sales.CreditCard table

INSERT INTO [AdventureWorks].[Sales].[CreditCard]

           ([CardType]

           ,[CardNumber]

           ,[ExpMonth]

           ,[ExpYear]

           ,[ModifiedDate])

     VALUES

           ('VISA'

           ,'11112222847802'

           ,12

           ,2006

           ,GETDATE())

 

--UPDATE

--updating TaxRate for Province with ID=57 in Sales.SalesTaxRate table

--Make sure you use a WHERE clause otherwise it will do a Global Update

UPDATE [Sales].[SalesTaxRate]

   SET TaxRate = 8

WHERE StateProvinceID=57

--DELETE

--deleting a row in Sales.Customer table

--Make sure you use a WHERE clause otherwise it will do a Global Delete

DELETE FROM [Sales].[Customer]

  WHERE [CustomerID]=4

/* 5 -----------------------------Script-----------------------------

This script will create the database HR

Before running the script be sure that the data and log files are in the correct path for the drives on the server.

*/

-----------------------------------------------------------------*/

USE master

GO

CREATE DATABASE HR --Name of the database

ON

( NAME =HR_data, -- Logical name for Data File

   FILENAME = 'C:\dba\datafile\HR_data.mdf', -- Physical location for Data File

   SIZE = 100, -- Initial File Size in MB (Mega Bytes)

   MAXSIZE = 250, -- Maximum File Size in MB (Mega Bytes)

   FILEGROWTH = 10% ) --Filegrowth parameter using percent

LOG ON

( NAME = 'HR_log', -- Logical name for Log File

   FILENAME = 'C:\dba\datafile\HR_log.ldf', -- Physical location for Log File

   SIZE = 100MB, -- Initial Size in MB (Mega Bytes)

   MAXSIZE = 200MB, -- Maximum File Size in MB (Mega Bytes)

   FILEGROWTH = 10MB )--Filegrowth parameter using MB (Mega Bytes)

GO

Disclaimer:

All sample code and scripts are compiled for illustrative purposes only. therefore, cannot guarantee or imply reliability, or function of this code or scripts.

All code contained herein are provided to you "AS IS" without any warranties of any kind. Please test all code and scripts in test environment before deployment in production systems.