SQL SERVER - DROP,DELETE,TRUNCATE,SELECT all the tables in the Database

Using sp_MSforeachtable stored procedure we can empty all table or delete all data from all the tables in the database. We can do this with a single line of statement to do these entire action.
   
Let's first create a test database and table to test.


USE master
GO
CREATE DATABASE TestDataBase
GO

USE TestDataBase
GO
CREATE TABLE [dbo].[Account]
( [account_number] [varchar](6) NULL,
[branch_name] [varchar](6) NULL,
[balance] [numeric](8, 2) NULL
) ON [PRIMARY]

USE TestDataBase
GO
INSERT INTO [dbo].[Account]([account_number],[branch_name],[balance])
VALUES('ACC001','B00001',123.45)
GO


--To select all records from all tables in the database:

EXEC sp_MSforeachtable @command1 = 'SELECT * FROM ?'


--To delete all records in all tables in the database:

EXEC sp_MSforeachtable @command1 = 'DELETE FROM ?'

--To Truncate all tables in the database:

EXEC sp_MSforeachtable @command1 = 'TRUNCATE TABLE ?'

--To drop all the tables:
EXEC sp_MSforeachtable @command1 = 'DROP TABLE ?'

SQL SERVER - ACID Rules (Atomicity, Consistency, Isolation, Durability)

ACID property that is Atomicity, Consistancy, Isolation, Durability

Atomicity means the transaction must be executed all or none. Here partial execution of transaction must not be done.The transfer of funds can be completed or it can fail for a multitude of reasons, but atomicity guarantees that one account won't be debited if the other is not credited as well.

Consistency ensures that the database is stable, before and after the transaction, even if a transaction fails. the output must be consistent, i.e transaction never leaves your database in a half-finished state.

Isolation means multiple transactions occurring at the same time will not impact each other’s execution.  The transaction must be in isolation that is in parallel.

Durability refers to the guarantee that once the user has been notified of success, the transaction will persist, and not be undone. The database will keep track of pending changes in such a way that the server can recover from an abnormal termination.This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction.

SQL SERVER - Refreshing the IntelliSense Cache

Today, I have created one table and tried to create a stored procedure to insert data into this new table in another tab, when I type INSERT INTO then the Intellisense drop down doesn't present me with my new table.

There are cases where the local cache used by IntelliSense becomes stale. Refreshing the cache is easy but not necessarily obvious.

There are two ways to refresh the cache:

1. Hit Ctrl+Shift+R
2. Go to Edit -> IntelliSense -> Refresh Local Cache and

If IntelliSense is not working still, then make sure you have IntelliSense enabled, to check this from the T-SQL Query editor window of current database,
1. Go to Tools -> Options -> Text Editor -> Transact-SQL -> General -> IntelliSense
2. Go to Query -> IntelliSense should be selected
3. Also verify that the T-SQL Editor does not launch in SQLCMD Mode, to check go to Tools -> Options -> Query Execution -> SQL Server -> General, make sure “By default, open new queries in SQLCMD mode” is unchecked.
or Go to Query ->  SQLCMD Mode  should not be selected


Still IntelliSense is not working then sounds like you have a corrupt installation, then re-installing the tools would be recommended, in case something was installed after the fact to change the binaries.


Also make sure you are connected to SQL Server 2008 Edition, because IntelliSense does not work with the previous versions of SQL Server.

SQL SERVER - Inline Variable Assignment in Sql Server 2008

In SQL 2005 variable assignment is done by:

DECLARE @CurrentDate_in_2005 DATETIME
SET @CurrentDate_in_2005 = GETDATE()
SELECT @CurrentDate_in_2005 AS CurrentDate_in_2005


But in SQL Server  2008 we can do it in one line:

DECLARE @CurrentDate_in_2008 DATETIME = GETDATE()
SELECT @CurrentDate_in_2008 AS CurrentDate_in_2008

SQL SERVER - Different Ways To Identify Structure of a Table

We can know the structure of the table to extract the Column names, types, max length, etc. using following different methods.

In new query analyser window type the name of a table, highlight the name of the table and then press ALT + F1

EXEC SP_HELP 'TableName'

EXEC SP_COLUMNS 'TableName'

SELECT * FROM information_schema.columns WHERE table_name = 'TableName'


Generate SQL Script option from Enterprise Manager or Management Studio
On the Choose Objects page, Right click on database name -> select option 'Tasks' -> Generate Scripts select the objects to be included in the script.

SQL SERVER - STUFF() Function

The STUFF function inserts a given string into another string. This function deletes a specified length of characters from the first string at the start position and then inserts the second string into the first string at the start position.

General Syntax:
STUFF ( CharacterExpression , StartPos , length ,CharacterExpression )

Example:

SELECT STUFF('FunctionStuff()', 1, 8, 'ExampleOf')

GO

The result of this query is: 

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

ExampleOfStuff()

CharacterEexpression

It is an expression of character data.  CharacterEexpression can be a constant, variable, or column of either character or binary data.

StartPos  
Is an integer/bigint value that specifies the location to start deletion and insertion.

length  

Is an integer/bigint that specifies the number of characters to delete.

It returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

The use of this function can be found to get Comma Separated Values

SQL SERVER - Find Database File and Log Path

I need to find the place where I have all the .mdf and .ldf files are stored. The following is the simple SQL query to find out where .mdf and .ldf files are stored. This list helps to find database file and log path.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

Following is the output for the files used by SQL Server instance.

For more details, please visit: http://msdn.microsoft.com/en-us/library/ms186782.aspx