Drop all objects in a database on SQL Server

Solution for Drop all objects from database at once!

Following procedure run on any database (SQL Server)

\***********************************************/


CREATE PROCEDURE dbo.DropAllDBObjects
@Action VARCHAR(20)=NULL
As
BEGIN

DECLARE @Name VARCHAR(500)

IF @Action='Table'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.TABLES

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP TABLE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END

ELSE IF @Action='PROCEDURE'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
IF @Name <> 'DropAllDBObjects'
EXEC('DROP PROCEDURE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END
ELSE IF @Action='Function'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP FUNCTION ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END
ELSE IF @Action='View'
BEGIN
DECLARE DROPALL CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP VIEW ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

END
IF @Action is null
BEGIN
--Table
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.TABLES

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP TABLE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

--Views
DECLARE DROPALL CURSOR
FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP VIEW ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

--Functions
DECLARE DROPALL CURSOR
FOR SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
EXEC('DROP FUNCTION ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL

--Procedure
DECLARE DROPALL CURSOR
FOR SELECT [NAME] FROM sys.objects WHERE TYPE='P'

OPEN DROPALL
FETCH NEXT FROM DROPALL INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
IF @Name <> 'DropAllDBObjects'
EXEC('DROP PROCEDURE ' + @Name)
FETCH NEXT FROM DROPALL INTO @Name
END
CLOSE DROPALL
DEALLOCATE DROPALL
END
END
GO

\***********************************************/

To execute Procedure:

a)To Drop all objects from Database,execute following

EXEC DropAllDBObjects

b)To Drop all objects from Database,based on different object at time.

EXEC DropAllDBObjects 'table'

or

EXEC DropAllDBObjects 'View'

or

EXEC DropAllDBObjects 'Function'

or

EXEC DropAllDBObjects 'Procedure'


To View all objects,use following

SELECT [NAME] FROM sys.TABLES
SELECT [NAME] FROM sys.objects WHERE TYPE='P'
SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

After All work done,you can drop following procedure

DROP PROC DropAllDBObjects

Differences between varchar and nvarchar in SQL Server

The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are VARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference?

VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.

The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.

The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.

Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

Windows Vista Error Fix Desktop Icons And Taskbar Fails To Load At Startup

There are really a lot of reasons why all of your icons and your Windows Vista desktop fails, all of a sudden, to load at startup. Some times the taskbar also refuses to load at Windows Vista startup.Anyway, most of the times this issue is due to a little key in the registry called explorer.exe which refuses to load or it is just missing or it doesn’t exist at all.This Issue in Windows Vista is easily fixed by using one of these 4 fixes:

The following method i have checked out and its worked for my laptop inspiron 1545 windows vista home edition.

Method1:

Copy and paste the following piece of code in your notepad:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]
“Userinit”=”C:\\WINDOWS\\SYSTEM32\\userinit.exe,”

Now save the file as Userinit.reg. Double-click on it to merge it in the Windows Vista registry. or Change registry key value and close registry editor and restart.

Method 2:

The key Explorer.exe in your registry is not correct.To fix this:

• Click on Windows Vista Start.
• In the start menu Search Box type “regedit” and hit Enter.
• In Windows registry, locate the following key: [HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\Windows NT\CurrentVersion\ Image File Execution Options]
• Look for Explorer.exe.
• Delete it.
• or Change registry key value to Explorer.exe to the above location and close registry editor then restart.

Method 3:

Restore Explorer.exe which is missing in your Windows Vista registry.To do this:

• Copy this piece of code in your notepad editor.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon]
“Shell”=”explorer.exe”

• Save the file as Logon.reg.
• Double-click on it to merge it in the Windows Vista registry(Sometimes UAC prompt appears and click “Continue”).
• or Change registry key value to Explorer.exe to the above location and close registry editor then restart.

Method 4:

If explorer.exe is not in C:\WINDOWS\ then insert your Windows CD and, using the ‘Task Manager’ (File -> New Task), run the ‘System File Checker’ utility with this command:

sfc /scannow

If the ‘System File Checker’ didn’t replace it, boot to the Recovery Console with the Windows CD and copy explorer.exe manuallyFor this:

• Insert your Windows Vista DVD.
• When the setup screen window appears, choose the Repair or Recover option by pressing “R”.
• In the Command Prompt, type the following command: expand G:\i386 /F:EXPLORER.EX_ C: \WINDOWS\explorer.exe /y (don’t forget that G: is the letter of your DVD drive, while C: is the partition where Windows Vista is installed, change them if they are different in your PC)

original link from:

http://www.tomstricks.com/windows-vista-error-fixdesktop-icons-and-taskbar-fails-to-load-at-startup/

Identify last statement run for a specific SQL Server session

SELECT DEST.TEXT
FROM sys.[dm_exec_connections] SDEC
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle]) AS DEST
WHERE SDEC.[most_recent_session_id] = (SELECT @@SPID)

RESEED All Identity Column in all tables of a Database

IF EXISTS(SELECT *FROM SYS.PROCEDURES WHERE NAME='SP_RESEEDIdentityColumn')
BEGIN
DROP PROC SP_RESEEDIdentityColumn
END
GO
CREATE PROC SP_RESEEDIdentityColumn
@RESEEDVALUE INT=0
AS
BEGIN
DECLARE CURRESEED CURSOR
FOR
SELECT NAME FROM SYS.TABLES

DECLARE @TABNAME VARCHAR(225),@SQL NVARCHAR(225)

OPEN CURRESEED
FETCH NEXT FROM CURRESEED INTO @TABNAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL ='DBCC CHECKIDENT('+@TABNAME+',RESEED,'+@RESEEDVALUE+')'
EXEC(@SQL)
FETCH NEXT FROM CURRESEED INTO @TABNAME
END
END

CLOSE CURRESEED
DEALLOCATE CURRESEED
GO


--Here if you are not specified param value it reseed from 0
EXECUTE SP_RESEEDIdentityColumn

--Here if you are not specified param value it reseed from 1
EXECUTE SP_RESEEDIdentityColumn 1

Delete the data from all tables In Database-Simple Sql Server Query!

USE TEST
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Different String Function in SQL Server 2005

ASCII function in SQLServer

Returns the ASCII code value of Leftmost charector in a string
Syntax : ASCII ('char Expression')

NCHAR function in SQLServer
Returns the Unicode Charector with specified Integer code, as defined be Unicode standard
Syntax :NCHAR(Integer Expression)

SOUNDEX function in SQLServer
Returns a four-charector code to evalute the similarity of two strings
Syntax : SOUNDEX('char Expression')
Ex:
Select SOUNDEX('smith'),SOUNDEX('smythe')
Result:
S530 S530

CHAR function in SQLServer
Convert an Int ASCII code to a Charector
Syntax : CHAR( Integer Expression )
Note : Is an integer from 0 through 255, NULL is return if the integer expression not in the Range

PATINDEX function in SQLServer
Returns the starting position of the first occurance of a pattren in the specied expression, or Zero if the pattren is nor found the specified expression, on all valid text and charector data types.
Syntax : PATINDEX('%pattren%',expression)

SPACE function in SQLServer
Returns a string of Repeated Spaces
Syntax : SPACE (Integer Expression)
Ex:
Select 'Myname' + Space(2) + 'Lastname'
Result:
Myname Lastname

QUOTENAME function in SQLServer
Returns a Unicode string with the delimiters added to make the input string a valid Microsoft SQL Server delimited identifier
Syntax: QUOTENAME ( 'character_string' [ , 'quote_character' ] )

' character_string '

Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

' quote_character '

Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.

Ex:
Select QUOTENAME('abc[]def')
Result :
[abc[]]def

STR function in SQLServer
Returns characters data converted from numaric data
Syntax : STR (float Expression [,length,[[decima]])
Ex;
Select STR(123.45,6,1)
Result
123.4

DIFFERENCE function in SQLServer
Retruns interger value that indicates the difference between SOUNDEX value of two character expressions
Syntax : DIFFERENCE (firat expression, second Expression)

REPLACE function in SQLServer
Replaces all occurences of Specified string value with another string value
Syntax : Replace (car expression,old string,new string)
EX:
Print REPLACE('abcdefghcd','c','x')
Result:
abxdefghxd

STUFF function in SQLServer
Deletes a specied length of characters and insert another set of characters at specified starting point
Syntax : STUFF(char expression,start,length,char expression)
Ex :
select STUFF('abcdef',2,3,'ijklmn')
Result:
aijklmnef


LEFT function in sqlserver
Return the left part of the char expression with specied number of characters
Syntax : LEFT(char expression,length)
Ex:
print LEFT('abcdef',2)
Result:
ab

REPLICATE function in SQLServer
Replicate a string value in specified numver of times
Syntax : REPLICATE(char expression, interger expression)
Ex:
Print REPLICATE('0',4) + 'S'
Result:
0000S

SUBSTRING function in SQL Server
Returns a part of charecter, byte, text or Image expression
Syntax : SUBSTRING(char expression,start,length)
Ex:
select SUBSTRING('myname',1,2)
Result:
my

LEN function in SQL Server
Returns length of specified char expression, excluding trailing blank spaces
Syntax : LEN(char expression)
Ex :
print LEN(' my Name')
Result :
7


REVERSE function in SQL Server
Returns reverse of char expression
Syntax : REVERSE(char expression)

Create your own key in Registry using Microsoft.Win32.RegistryKey in c#.Net

//Check for key exists in Registry before creating New key and if not exists then create new key

if (((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))) == null)

{

Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.CurrentUser.CreateSubKey("MyChandruKey123");

key.SetValue("MyConnStrChandru123","");

}

//Get the Key value based on key name

string conns = ((Microsoft.Win32.RegistryKey)(Microsoft.Win32.Registry.CurrentUser.OpenSubKey("MyChandruKey123"))).GetValue("MyConnStrChandru123").ToString();

Encrypt and Decrypt the string using RSACryptoServiceProvider in c#.Net within Single Line of Code

//Call the functions

string EncryptedString= GetEncryptedText("Chandru");

string DecryptedString = GetDecryptedText(EncryptedString);

//Create an object for RSACryptoServiceProvider

RSACryptoServiceProvider RSAEncrypt = new RSACryptoServiceProvider();

//Encrypt the String Provided

public string GetEncryptedText(string PlainStringToEncrypt)

{

return Encoding.Default.GetString(RSAEncrypt.Encrypt(Encoding.Default.GetBytes(PlainStringToEncrypt), false));

}

//Decrypt The String Provided

public string GetDecryptedText(string EncryptedStringToDecrypt)

{

return Encoding.Default.GetString(RSAEncrypt.Decrypt(Encoding.Default.GetBytes(EncryptedStringToDecrypt), false));

}

Access Table withought linking Servers

Select A.* From OPENROWSET('MSDASQL', 'DRIVER={SQL SERVER};SERVER=name\instance;UID=user;PWD=password',

'SELECT * FROM pubs.dbo.authors') AS A Where A.MaintenanceDate <> MaintenanceDate

Select A.* From OPENROWSET('MSDASQL',

'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass',

'SELECT * FROM database.dbo.table') as A

Right Outer Join database.dbo.table As B on A.PlantID = B.PlantID

AND A.RuleType = B.RuleType AND A.RulePrefix = B.RulePrefix and

A.RuleSuffix = B.RuleSuffix

Where A.MaintenanceDate <> B.MaintenanceDate




SELECT A.*
FROM database.dbo.table AS B
LEFT JOIN OPENROWSET('MSDASQL', 'Driver={SQL SERVER};SERVER=server\instance;UID=user;PWD=pass'
,'SELECT * FROM database.dbo.table') AS A
ON B.PlantID = A.PlantID AND B.RuleType = A.RuleType AND B.RulePrefix = A.RulePrefix
AND B.RuleSuffix = A.RuleSuffix AND B.MaintenanceDate <> A.MaintenanceDate