CopyPaste.Net: November 2010

Backup and Restore DB

BACKUP DATABASE [ItaiTest]
TO DISK = N'C:\TempItai\ItaiTest.bak'
WITH NOFORMAT, NOINIT, NAME = N'ItaiTest-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

CREATE DATABASE [ItaiTestNew]
GO

RESTORE DATABASE [ItaiTestNew]
FROM DISK = N'C:\TempItai\ItaiTest.bak'
WITH FILE = 1,
MOVE N'ItaiTest' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ItaiTestNew.mdf',
MOVE N'ItaiTest_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ItaiTestNew_log.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO

Find differences between types of parameters in SPs and types of the real fields

declare @dbname sysname

set @dbname = 'Campus'

declare @cmd varchar(max)

set @cmd = 'use ' + @dbname + ';

select distinct d.id, OBJECT_NAME(d.id) as ProcedureName, d.depid, OBJECT_NAME(d.depid) as TableName

, c.name as FieldName, t.name as FieldType, c.scale, c.prec

into #tempColumns

from sys.sysdepends d

inner join sys.sysobjects o1

on d.id = o1.id

inner join sys.sysobjects o2

on d.depid = o2.id

inner join sys.syscolumns c

on d.depid = c.id

inner join sys.systypes t

on c.xtype = t.xtype

where OBJECT_NAME(d.id) not like ''sp_%''

and o1.xtype = ''P''

and o2.xtype = ''U''

select object_name(O.id) As ProcedureName, replace(C.name,''@'','''') as InputParameter

,t.name as FieldType, c.scale, c.prec

into #tempParameters

from sys.sysobjects O

inner join sys.syscolumns C

on C.id = O.id

inner join sys.systypes t

on c.xtype = t.xtype

where O.xtype = ''P''

order by 1

select tc.ProcedureName, tc.TableName,

tc.FieldName, tc.FieldType, tc.scale, tc.prec,

(''@'' + tp.InputParameter) as InputParameter,

tp.FieldType, tp.scale, tp.prec

from #tempColumns tc

inner join #tempParameters tp

on tc.ProcedureName = tp.ProcedureName

and tc.FieldName = tp.InputParameter

where

tc.FieldType != tp.FieldType

order by 1,2,3'

exec (@cmd)

Schema - create, alter, drop

CREATE SCHEMA schema_clause [ schema_element [ , ...n ] ]

ALTER SCHEMA newschema TRANSFER oldschema.Table

SELECT 'ALTER SCHEMA dbo TRANSFER ' + SCHEMA_NAME(schema_id) + '.' + name

FROM sys.tables -- /FROM sys.objects
WHERE schema_id != SCHEMA_ID('dbo')

DROP SCHEMA schema

Create a test database

--Create a test database include: login, user, role, schema, and a simple table:

--create a test database

CREATE DATABASE [SecurityTest]

USE SecurityTest

CREATE TABLE [dbo].[table1](

[pkcol] [int] IDENTITY(1,1) NOT NULL,

[col1] [int] NULL,

PRIMARY KEY CLUSTERED ([pkcol])

)

--create test user login

CREATE LOGIN [User1] WITH PASSWORD=N'p@55w0rd'

--create user in test database

CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[Developer_Schema]

--create role

CREATE ROLE [Developer_Role] AUTHORIZATION [dbo]

--create schema

CREATE SCHEMA [Developer_Schema] AUTHORIZATION [User1]

--apply permissions to schemas

GRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]

GRANT DELETE ON SCHEMA::[dbo] TO [Developer_Role]

GRANT INSERT ON SCHEMA::[dbo] TO [Developer_Role]

GRANT SELECT ON SCHEMA::[dbo] TO [Developer_Role]

GRANT UPDATE ON SCHEMA::[dbo] TO [Developer_Role]

GRANT REFERENCES ON SCHEMA::[dbo] TO [Developer_Role]

--ensure role membership is correct

EXEC sp_addrolemember N'Developer_Role ', N'User1'

--allow users to create tables in Developer_Schema

GRANT CREATE TABLE TO [Developer_Role]

--Allow user to connect to database

GRANT CONNECT TO [User1]

$PARTITION

SELECT $PARTITION.pf_Orders(OrderDate) AS Partition,

COUNT(*) AS [COUNT] , Min(Orderdate) [from], Max(OrderDate) [to]

FROM dbo.NewSales

GROUP BY $PARTITION.pf_Orders(OrderDate)

ORDER BY Partition

SET STATISTICS IO

SET STATISTICS IO { ON | OFF }

Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical

reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,

lob read-ahead reads 0.

Error: User xxx already Exists

DECLARE @UserName nvarchar(255)

DECLARE orphanuser_cur cursor for

SELECT UserName = name FROM sysusers

WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null

ORDER BY name

OPEN orphanuser_cur

FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)

BEGIN

PRINT @UserName + ' user name being resynced'

EXEC sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName

END

CLOSE orphanuser_cur

DEALLOCATE orphanuser_cur

sp_change_users_login: Maps an existing database user to a SQL Server login. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

http://msdn.microsoft.com/en-us/library/ms174378.aspx

ALTER USER: Renames a database user or changes its default schema:

ALTER USER Mary5 WITH NAME = Mary51

ALTER USER Mary51 WITH DEFAULT_SCHEMA = Purchasing

http://msdn.microsoft.com/en-us/library/ms176060.aspx

Insert random data

CREATE TABLE data (OrderID INT IDENTITY(1,1) PRIMARY KEY, OrderDate DATETIME,

CustomerID INT, ProductCode VARCHAR(50), Amount INT)

/*insert 100,000 rows of random data*/

INSERT INTO data (OrderDate, CustomerID, ProductCode, Amount)

SELECT TOP 100000 GETDATE() - ABS(CHECKSUM(NEWID())) % 730,

ABS(CHECKSUM(NEWID())) % 10000 + 1,

char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26) + char(65+ ABS(CHECKSUM(NEWID())) % 26),

ABS(CHECKSUM(NEWID())) % 100

FROM master..sysobjects a cross join master..sysobjects b

Re-Index

DBCC DBREINDEX

(

table_name

[ , index_name [ , fillfactor ] ]

)

[ WITH NO_INFOMSGS ]

-- Rebuilding an index

DBCC DBREINDEX ("HumanResources.Employee", PK_Employee_BusinessEntityID,80)

-- Rebuilding all indexes (of a table)

DBCC DBREINDEX ("HumanResources.Employee", " ", 70)

Find all unused indexes in the current database

select t.name as TableName, i.name as IndexName,
DB_NAME(s.database_id) as DatabaseName
from sys.indexes i
inner join sys.dm_db_index_usage_stats s on s.object_id = i.object_id
and s.index_id = i.index_id
inner join sys.tables t on i.object_id = t.object_id
where ( (user_seeks = 0 and user_scans = 0 and user_lookups = 0)
or
s.object_id is null
)
and s.database_id = DB_ID(' DbName')

2) This query builds a list of indexes that could benefit from ALTER INDEX REBUILD or ALTER INDEX REGORGANIZE.

select t.name as TableName

, i.name as IndexName

from sys.indexes i

inner join sys.dm_db_index_usage_stats s on s.object_id = i.object_id and s.index_id = i.index_id

inner join sys.tables t on i.object_id = t.object_id

inner join #frag_indexes f on i.object_id = f.object_id and i.index_id = f.index_id

order by s.user_scans desc, f.priority asc

Analyze missing Indexes - sys.dm_db_missing_index_details

sys.dm_db_missing_index_details - Returns indexes the optimizer considers are missing.

sys.dm_db_missing_index_columns - Returns the columns for a missing index.

sys.dm_db_missing_index_group_stats - Returns usage and access details for the missing indexes similar to sys.dm_db_index_usage_stats

The following query returns a prioritized list of the missing indexes in the current database:

SELECT so.name

, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact

, mid.equality_columns

, mid.inequality_columns

, mid.included_columns

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle

INNER JOIN sys.objects so WITH (nolock) ON mid.object_id = so.object_id

WHERE migs.group_handle IN (

SELECT TOP (5000) group_handle

FROM sys.dm_db_missing_index_group_stats WITH (nolock)

ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)

http://msdn.microsoft.com/en-us/library/ms345434.aspx

top 10 worst CPU consuming queries on the SQL Server

The following query pulls the top 10 worst CPU consuming queries on the SQL Server (since the last time the server was restarted):

SELECT TOP 10

sql.text as sql

, qp.query_plan

, creation_time

, last_execution_time

, execution_count

, (total_worker_time / execution_count) as avg_cpu

, total_worker_time as total_cpu

, last_worker_time as last_cpu

, min_worker_time as min_cpu

, max_worker_time as max_cpu

, (total_physical_reads + total_logical_reads) as total_reads

, (max_physical_reads + max_logical_reads) as max_reads

, (total_physical_reads + total_logical_reads) / execution_count as avg_reads

, max_elapsed_time as max_duration

, total_elapsed_time as total_duration

, ((total_elapsed_time / execution_count)) / 1000000 as avg_duration_sec

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) sql

CROSS APPLY sys.dm_exec_query_plan (plan_handle) qp

ORDER BY qs. total_worker_time DESC

Find the server IO load (MSSQL)

sys.dm_io_virtual_file_stats ({ database_id | NULL }, { file_id | NULL })

SELECT sys.dm_io_virtual_file_stats.database_id,

SUBSTRING(name,1,20) AS 'Database',

CASE FILE_ID WHEN 1 THEN 'Data'

WHEN 2 THEN 'Log'

END AS 'Type',

num_of_reads,

num_of_bytes_read,

num_of_writes,

num_of_bytes_written,

size_on_disk_bytes,

io_stall_read_ms,

io_stall_write_ms,

io_stall

FROM sys.dm_io_virtual_file_stats(null,null),

sys.databases

WHERE sys.databases.database_id = sys.dm_io_virtual_file_stats.database_id

sys.dm_io_virtual_file_stats columns:

database_id ID of database.

file_id ID of file.

sample_ms Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function.

num_of_reads Number of reads issued on the file.

num_of_bytes_read Total number of bytes read on this file.

io_stall_read_ms Total time, in milliseconds, that the users waited for reads issued on the file.

num_of_writes Number of writes made on this file.

num_of_bytes_written Total number of bytes written to the file.

io_stall_write_ms Total time, in milliseconds, that users waited for writes to be completed on the file.

io_stall Total time, in milliseconds, that users waited for I/O to be completed on the file.

size_on_disk_bytes Number of bytes used on the disk for this file. For sparse files, this number is the actual number of bytes on the disk that are used for database snapshots.

file_handle Windows file handle for this file.

for MSSQL 2000:

SELECT * FROM FN_VIRTUALFILESTATS(DEFAULT, DEFAULT)

http://www.sqlhacks.com/Optimize/Server-Load

http://www.databasejournal.com/features/mssql/article.php/3895221/Using-Dynamic-Management-Objects-to-Monitor-IOs-Against-an-Instance-of-SQL-Server.htm

WITH common_table_expression (CTE)

Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.

WITH PPP AS

(

SELECT .....

FROM ...

WHERE .....

UNION ALL

SELECT ....

FROM ..... INNER JOIN .... ON .....

)

UPDATE Production.BillOfMaterials

SET PerAssemblyQty = c.PerAssemblyQty * 2

FROM Production.BillOfMaterials c

JOIN PPP d ON c.ProductAssemblyID = d.AssemblyID

WHERE d.ComponentLevel = 0

WITH vw AS

(

SELECT ....

)

,r AS

(

SELECT .....

)

..........

http://msdn.microsoft.com/en-us/library/ms175972.aspx

Use it in update:

http://msdn.microsoft.com/en-us/library/ms177523.aspx

Dynamic SQL

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT .......... '

EXEC (@SQL)

EXEC sp_executesql @SQL,

N' @FromTime date,

@OIdentity nvarchar(50),

@Status int,

@Name nvarchar(50)',

@FromTime = @FromPlanStrTime,

@OIdentity = @OIdentity,

@Status = @Status,

@Name = @Name

Convert From Binary to Decimal

1. Get the last digit of the hex number, call this digit the currentDigit.

2. Make a variable, let's call it power. Set the value to 0.

3. Multiply the current digit with (2^power), store the result.

4. Increment power by 1.

5. Set the the currentDigit to the previous digit of the hex number.

6. Repeat step 3 until all digits have been multiplied.

7. Sum the result of step 3 to get the answer number.

1010001 = 81

(1*1) + (0*2) + (0*4) + (0*8) + (1*16) + (0*32) + (1*64) =

1 + 0 + 0 + 0 + 16 + 0 + 64 = 81

Binary / Decimal Converter Calculator:

http://mistupid.com/computers/binaryconv.htm

"There are 10 types of people in this world. Those who do understand binary and those who don't." :-)

Convert From Decimal to Binary

1. Determine if your decimal number is 0 or 1. If it is either of these numbers, the binary number is the same as the decimal number. If it is not either of these numbers, move on to Step 2.

2. Find the largest possible power of 2 that can be subtracted from the decimal number without making it negative. Then, subtract it.

3. Write a "1" as the first digit of your binary number.

4. Decide whether you can subtract the next smallest power of 2 from what is left of the decimal number without making it negative.

5. Deduct this power of 2 from the decimal number and write "1" as the next digit of your binary number if your results were positive for Step 5. If they were negative, write "0" as the next digit of your binary number.

6. Repeat Step 5 and 6 until you reach the number 0. The numbers you write down during this process are the binary number.

Binary / Decimal Converter Calculator:

http://mistupid.com/computers/binaryconv.htm

"There are 10 types of people in this world. Those who do understand binary and those who don't." :-)

Mathematical & Binary Operators & Functions

Operators
+ addition

- subtraction
* multiplication
/ division
% modulo (remainder) 5 % 4 1
^ exponentiation
|/ square root
||/ cube root
! factorial 5 !=120
!! factorial (prefix operator) !!5=120
@ absolute value
& binary AND
| binary OR
# binary XOR
~ binary NOT
<< binary shift left
>> binary shift right

Bit String Binary Operators
B'10001' & B'01101' = 00001
B'10001' | B'01101' = 11101
B'10001' # B'01101' = 11110
~ B'10001' = 01110
B'10001' << 3 = 01000
B'10001' >> 2 = 00100

Functions
abs(x) - absolute value
cbrt(dp) - cube root
ceil(dp or numeric) - smallest integer not less than argument
degrees(dp) - radians to degrees
exp(dp or numeric) - exponential
floor(dp or numeric) - largest integer not greater than argument
ln(dp or numeric) - natural logarithm
log(dp or numeric) - base 10 logarithm
log(b numeric, x numeric) - logarithm to base b
mod(y, x) - remainder of y/x
pi() - "Pi" constant
pow(x dp, e dp) - raise a number to exponent e
pow(x numeric, e numeric) - raise a number to exponent e
radians(dp) - degrees to radians
random() - random value between 0.0 and 1.0
round(dp or numeric) - round to nearest integer
round(v numeric, s integer) - round to s decimal places
sign(dp or numeric) - sign of the argument (-1, 0, +1)
sqrt(dp or numeric) - square root
trunc(dp or numeric) - truncate toward zero
trunc(v numeric, s integer) - truncate to s decimal places

Execute Batch more than once

GO XXX

SELECT 'aaa'

GO 100 -- the select will be executed 100 times

High Avg. Disk Queue Length

The fact that the line stays at the top of the graph does not necessarily indicate there is a problem. The default Avg. Disk Queue Length graph scale is 100 so any value >= 1 will peg the graph.

As a general rule, Avg. Disk Queue Length should not consistently exceed 2 times the number of physical disks in the array. A higher number indicates a disk bottleneck. This can be:
1) due to excessive paging (are other apps besides SQL Server running on the box)
2) an indicator that database tuning is needed (e.g. adding indexes)
3) cause by inadequate number of disks to satisfy workload

http://www.sqlnewsgroups.net/sqlserver/t9582-slow-performance-high-avg-disk-queue-length.aspx

Using Performance Monitor

http://www.sql-server-performance.com/tips/performance_monitor_general_p1.aspx

http://www.sql-server-performance.com/tips/monitor_io_counters_p1.aspx

Identify SQL Server Hardware Bottlenecks

Performance Monitor Counters:

- Memory: Pages/sec

- Memory: Available Bytes

- Physical Disk: % Disk time

- Physical Disk: Avg. Disk Queue Length

- Processor: % Processor Time

- System: Processor Queue Length

- SQL Server Buffer: Buffer Cache Hit Ratio

- SQL Server General: User Connections

http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx

http://kinglior.wordpress.com/2009/11/26/performance-counters-%D7%9C%D7%A0%D7%99%D7%98%D7%95%D7%A8-%D7%A9%D7%A8%D7%AA-sql-server/

JS - print keycode value

document.onkeydown = checkKeycode

function checkKeycode(e) {

var keycode;

if (window.event) keycode = window.event.keyCode;

else if (e) keycode = e.which;

alert("keycode: " + keycode);

}

</script>--%>

Take answer even it NULL (Oracle)

where xxx = yyy (+)

Oracle DB export

exp full=y file=d1050338.dmp log=exp.log compress=n statistics=none consistent=y userid=system/pdsec@d1050338

exp full=n owner=super,pmx file=d1050338.dmp log=exp.log compress=n statistics=none consistent=y userid=system/pdsec@d1050338

Get NULLS first - Oracle

SELECT GENDER, TYPECAST, NAME FROM ACTOR

ORDER BY TYPECAST NULLS FIRST

In Oracle, when sorting with "order by", NULL values are sorted last. By write "NULLS FIRST" - NULL will be sorted in the beginning.

Few quick checks for DB objects and locks

exec sp_lock

exec sp_who 85

exec sp_who2 85

DBCC INPUTBUFFER (85)

select * from sys.sysprocesses where blocked > 0

select * from master.dbo.syslockinfo

SELECT * FROM master.sys.dm_tran_locks

select db_name(1)

select object_name(786101841)

SELECT * FROM master.sys.objects where object_id = 1115151018

select * FROM dbo.sysobjects where id=1115151018

exec sp_help 'Subjects'

select * from sysobjects where name like 'Subjects'

Select Case vb.net

Select Case ResString

Case "ActiveFeedingOrders"

.....

Case "0"

.....

Case else

.....

End Select 'Case

Try Parse

Integer.TryParse(aaaStr, aaaNumber)

TryParse return a boolean value - if the text (aaaStr) cab be converted to an int number. If the answer is TRUE - the int value will be set to aaaNumber.

VB.Net Logical Operators: AndAlso and OrElse

If (Not myObj Is Nothing) And (myObj.PropertyValue = "1") Then – Here we will FAIL when myObj is Nothing

If (Not myObj Is Nothing) AndAlso (myObj.PropertyValue = "1") Then – Here we will not get to the second condition when myObj is Nothing

Same logic apply for Or and OrElse

Oracle alter table

http://www.techonthenet.com/sql/tables/alter_table.php

Primary Keys:

http://www.techonthenet.com/oracle/primary_keys.php

MSSQL and Oracle data-types Compared

http://download.oracle.com/docs/cd/E10405_01/doc/appdev.120/e10379/ss_oracle_compared.htm#i1026427

SQL Server Email Alerts Notifications

--********************************

--Definitions: Run once

--********************************

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'MyMailAccount',

@description = 'Mail account for Database Mail',

@email_address = 'XXXX@aaa.co.il', --Replace

@display_name = 'MyAccount',

@username='XXXX@aaa.co.il', --Replace

@password='XXXX', --Replace

@mailserver_name = 'webmail' --Replace

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'MyMailProfile',

@description = 'Profile used for database mail'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'MyMailProfile',

@account_name = 'MyMailAccount',

@sequence_number = 1

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'MyMailProfile',

@principal_name = 'public',

@is_default = 1 ;

--********************************

--Execution: enter into SP

--********************************

EXEC msdb.dbo.sp_send_dbmail @recipients='XXXX@aaa.co.il',

@subject = 'My Mail Test',

@body = 'My First Database Email ',

@body_format = 'HTML' ;

GO

http://www.sql-server-performance.com/articles/dba/email_alerts_notification_p1.aspx

Some SQL Server System stored procedures

--provide list of all database objects

exec sp_help

--provide list of all columns , there datatype and some other important information

sp_help tablename_or_viewname

--Provide defination of given object, object can be function, SP or trigger

sp_helptext view_function_proc_triggername

-- provide list of current running process with some other important information

sp_who2

-- provide list of current running process, provide information less than sp_who2

sp_who

--provide list of tables and views

sp_tables

-- provide list of columnname and some other usefull information of input object

sp_columns table_viewname

-- provide list of all databases with there size

sp_databases

-- Enable you to rename a database or database object

sp_rename

currentname,

newname,

objecttype /*optional*/

--Enable you to set & view the database compatibility value , valid values for sql server 2005 are 60, 65, 70, 80, or 90 /*(90 for sql server 2005)*/

sp_dbcmptlevel

DatabaseName,

Value /*optional*/

--enable you to set values of different database option

sp_dboption dbname, optname, optvalue

--provide you the object dependent and type of dependent object for input object

sp_depends objectname

--enable you to equire a lock on active transaction

sp_getapplock Resource,

lockmode, --can be 'Shared', 'Update', 'Exclusive', 'IntentExclusive'and 'IntentShared'

LockOwner, --can be 'Transaction' and 'Session' -- default is 'Transaction'

LockTimeout, --default null

DbPrincipal -- default public

--provide you the detail of constraint on given objectname

sp_helpconstraint objectname

--provide you data and log file info for current database, if u pass a file name to this proc this will give you Db name for that file

sp_helpfile

[filename] --optional

--provide you file group name for current database

sp_helpfilegroup

filegroupname --optional

--provide you list of indexes on given object

sp_helpindex objectname

--provide you list of stats on given object

sp_helpstats objectname

--provide you list of triggers on given object

sp_helptrigger objectname

--provide you users for current Db with Loginname

sp_helpuser

--provide you detail of current locks

sp_lock

spId1, --optional

spId2 --optional

--Provide you overview of server performance

sp_monitor

--provide you list of database on which you have access rights

sp_mshasdbaccess

--provide you list of index on given object and space used by them

sp_msindexspace objectname

http://www.dotnetspider.com/resources/24257-usefull-SQL-Server-System-stored-procedure.aspx

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

Run following simple script on SQL Server 2005 to retrieve all stored procedure in database:

SELECT * FROM sys.procedures

This will ONLY work with SQL Server 2005.

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

System Stored Procedures (SQL Server 2000)

http://msdn.microsoft.com/en-us/library/aa259564(SQL.80).aspx

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

Query that returns list of all Stored Procedures in an MS SQL database

http://stackoverflow.com/questions/219434/query-that-returns-list-of-all-stored-procedures-in-an-ms-sql-database

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

SQL Script to check what was changed in database after specific hour Last night

SELECT name,

TYPE,

type_desc,

create_date,

modify_date

FROM sys.objects

WHERE TYPE IN ('U','V','PK','F','D','P')

AND modify_date >= Dateadd(HOUR,21,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME))

ORDER BY modify_date

--U - Table

--V - View

--PK - Primary Key

--F - Foreign Key

--D - Default Constraint

--P - Procedure

Get locked items informatiom

Use sp_lock in order to check what is locked now, and by who.

sp_lock2 is similar to sp_lock, except that it displays the database name, object name and index name instead of the ids

http://www.databasejournal.com/features/mssql/article.php/1442241/Detailed-Locking-View-splock2.htm

http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/30363/

COALESCE - Returns the first non-null expression among its arguments

COALESCE ( expression [ ,...n ] )

COALESCE returns the first non-null expression among its arguments.

If all arguments are NULL, COALESCE returns NULL.

Uses of Coalesce:

Get result of gradual condition:

SELECT COALESCE(hourly_wage, salary, commission) AS 'Total Salary' FROM SalariesTable

Concatenate column results to a string:

declare @StrList nvarchar(max) = ''

update SrcTableName set @StrList = COALESCE(@StrList,'') + COALESCE(TableColumnName, '') + ','

select @StrList=left(@StrList,len(@StrList)-1)

select @StrList

Using Coalesce to Execute Multiple SQL Statements:

DECLARE @SQL nvarchar(max)

SELECT @SQL=COALESCE(@SQL,'') +'Kill '+CAST(spid AS nvarchar(10))+ '; '

FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'DBNAME'

PRINT @SQL --EXEC(@SQL)

COALESCE VS. ISNULL

ISNULL accepts exactly two parameters. COALESCE can take multiple parameters.
The result of ISNULL always takes on the datatype of the first parameter COALESCE works more like a CASE expression, which returns a single datatype depending on all possible outcomes.
COALESCE result can be null, ISNULL always will return not-null result.

COALESCE VS. CASE

COALESCE simply returns the first value out of a list that is not NULL. Case can consider more Complicated conditions.
The COALESCE SQL Server function is very useful in constructing queries that evaluate multiple input parameters and can often be much faster than CASE statements.

http://msdn.microsoft.com/en-us/library/ms190349.aspx

Information about active connections for DBs

SELECT * FROM master..sysprocesses

SELECT db_name(dbid) as DatabaseName, *

FROM sys.sysprocesses

WHERE dbid > 0

and db_name(dbid) = 'vm5' -- if filter by DB name is required

SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName

FROM sys.sysprocesses

WHERE dbid > 0

GROUP BY dbid, loginame

order by db_name(dbid), loginame

sp_who 66
go
--kill 66
go

http://msdn.microsoft.com/en-us/library/ms179881.aspx

Get information about current users, sessions, and processes

sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

http://msdn.microsoft.com/en-us/library/ms174313.aspx
http://library.cirr.com/Microsoft/SQL-Server-v7/html/sp_wa-wz.htm

Get the last statement sent from a client

DBCC INPUTBUFFER:

Displays the last statement sent from a client to an instance of Microsoft SQL Server.

http://msdn.microsoft.com/en-us/library/ms187730.aspx

delay - WAITFOR

--Delay for 20 seconds

WAITFOR DELAY '000:00:20'

--Delay till 7 AM

WAITFOR TIME '7:00:00'

Space Usage

exec sp_spaceused -- for DB

exec sp_spaceused CI_Test -- for table

dbcc sqlperf(logspace) --Space Usage for Databases

EXCEPT and INTERSECT

When it is necessary to compare the two tables and find the exceptional lines, and the difference can be any of the columns, there are 2 commands (do not exist in mssql2000) that make it easier:

EXCEPT - Compare the 2 tabales and return the existing values in the first table, but not in the second table.

INTERSECT - Compare the 2 tabales and return only the shared values.

Note: like UNION, the tables for comparison should have the same structure - same columns, same data-types.

SELECT PartId, CutQtyRatio FROM @FirstMixer
EXCEPT
SELECT PartId, CutQtyRatio FROM @NextMixer

SELECT PartId, CutQtyRatio FROM @FirstMixer
INTERSECT
SELECT PartId, CutQtyRatio FROM @NextMixer

Force order of actions

option (force order) -- in the end of the query

Force the SQL the order of operations as we are registered and not the automatic order of operations of the SQL.

set XACT_ABORT ON - Rollback when transaction failed

set XACT_ABORT ON

begin tran

...........
insert......
select.....
update.....
...........

commit

If you run it and one or more statements in the transaction will failed - it does ROLLBACK automatically all that was in the transaction. Without it - actions that they can succeed - succeed and the others will failed.
The default value of XACT_ABORT is OFF, that why we have to activate it.

Replace for string

1. Replace
REPLACE(OrderId, 'FE', '')

2. Stuff
stuff(<org_text>,<from>,<to>,<str to insert instead>)

select OrderId, stuff(OrderId,1,1,'') as stuff_OrderId from CI_Order

Case

CASE

WHEN CloseID IS NULL THEN 100

ELSE CloseID

END

Create copy of a table without any data

Select *

into #tmpParents

FROM TABLE/FUNCTION/etc…...

WHERE 1=0

User Permission fix

EXEC sp_change_users_login 'Auto_Fix', conteldb

Fix User permissions that were run over by restore DB from other source with the same user name (as default the user is saved as new user!).

Times filter

Use:

WHERE StrTime <= @EndTime

AND @StrTime <= ISNULL(EndTime,'29991231')

Instead of:

WHERE

( (StrTime >= @StrTime and StrTime <= @EndTime) -- stoppage started while the given time and finished while or after the given time

OR (EndTime > @StrTime and EndTime <= @EndTime) -- stoppage finished while the given time and started while or before the given time

OR (StrTime < @StrTime and EndTime > @EndTime) -- stoppage started before the given time and finished after the given time

OR (StrTime < @StrTime and EndTime IS NULL) -- stoppage started before the given time and still not finished

)

fix error: "Cannot resolve collation conflict…"

In MS SQL SERVER, the collation can be set in column level. When compared 2 different collation column in the query, this error comes up.

SELECT ID FROM ItemsTable

INNER JOIN AccountsTable

WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add following keywords around “=” operator.

SELECT ID FROM ItemsTable

INNER JOIN AccountsTable

WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT

= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Collation can affect following areas:

1) Where clauses

2) Join predicates

3) Functions

4) Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

FOR XML EXPLICIT

SELECT 1 AS Tag,

NULL AS Parent,

e.EmployeeID AS [Employee!1!EmployeeID],

c.Name AS [Employee!1!Name],

NULL AS [Address!2!AddressLine1],

NULL AS [Address!2!PostalCode]

FROM HumanResources.Employee e(NOLOCK)

JOIN Person.Contact c (NOLOCK)

ON e.ContactID = c.ContactID

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

e.EmployeeID AS [Employee!1!EmployeeID],

NULL AS [Employee!1!Name],

AddressLine1 AS [Address!2!AddressLine1],

PostalCode AS [Address!2!PostalCode]

FROM HumanResources.Employee e(NOLOCK)

JOIN HumanResources.EmployeeAddress ea(NOLOCK)

ON e.EmployeeID = ea.EmployeeID

JOIN Person.Address a (NOLOCK)

ON ea.AddressID = a.AddressID

JOIN Person.StateProvince s (NOLOCK)

ON a.StateProvinceID = s.StateProvinceID

ORDER BY e.EmployeeID, Tag

FOR XML EXPLICIT, ROOT('Employees')

Result:

</Employee>

</Employee>

</Employees>

Check what was changed in DB after 9PM Last night

SELECT name, TYPE, type_desc, create_date, modify_date

FROM sys.objects

WHERE TYPE IN ('U','V','PK','F','D','P')

AND modify_date >= Dateadd(HOUR,21,Cast((Cast(Getdate() - 1 AS VARCHAR(12))) AS SMALLDATETIME))

ORDER BY modify_date

--U - Table

--V - View

--PK - Primary Key

--F - Foreign Key

--D - Default Constraint

--P - Procedure

Get the last new inserted ID

SCOPE_IDENTITY()
SELECT @AllocationLogHeaderID = SCOPE_IDENTITY()

Search text in SPs/Function/etc.

select object_name(id) as object_name_, count(*) as count_
from syscomments where text like '%TEXT%'
group by object_name(id) order by object_name(id)

-- same select, including schema name:

select schema_name(o.schema_id) as schema_, o.name, count(*) as count_
from syscomments c join sys.objects o on c.id = o.object_id
where text like '%TEXT%'
group by o.name, schema_name(o.schema_id)
order by schema_name(o.schema_id), o.name

-- same select, including objects types:

select CASE WHEN type in('P', 'PC', 'RF', 'X') THEN 'SP'

WHEN type in('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 'function'

WHEN type in('TA', 'TR') THEN 'trigger'

ELSE 'other'

END as objType, --o.type,

schema_name(o.schema_id) as schema_, o.name, count(*) as count_

from syscomments c join sys.objects o on c.id = o.object_id

where text like '%TEXT%'

group by o.type, o.name, schema_name(o.schema_id)

order by CASE WHEN type in('P', 'PC', 'RF', 'X') THEN 1

WHEN type in('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 2

WHEN type in('TA', 'TR') THEN 3

ELSE 10

END,

schema_name(o.schema_id), o.name

-- same select, including the object text:

select TT.objType, TT.schema_, TT.name,
Cast(( Select text As [text()]
From sys.syscomments T1
Where T1.ID=TT.ID
Order By colid
For XML Path('')) As XML) objText
FROM ( select c.id,
CASE WHEN type in('P', 'PC', 'RF', 'X') THEN 'SP'
WHEN type in('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 'function'
WHEN type in('TA', 'TR') THEN 'trigger'
ELSE 'other'
END as objType, --o.type,
schema_name(o.schema_id) as schema_, o.name, count(*) as count_
from syscomments c join sys.objects o on c.id = o.object_id
where text like '%TEXT%'
group by c.id,o.type, o.name, schema_name(o.schema_id)
) TT
order by CASE WHEN TT.objType = 'SP' THEN 1
WHEN TT.objType = 'function' THEN 2
WHEN TT.objType = 'trigger' THEN 3
ELSE 10
END,
TT.schema_,
TT.name

Labels