SQL SERVER - Single DROP statement to drop multiple tables

We can drop multiple tables using a single DROP statement. For example, we have three tables Table1, Table2, Table3, we want to drop all these tables, and we can do it by following statement:

DROP TABLE Table1, Table2, Table3

Following is an example to drop multiple tables using single DROP statement:

CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO

CREATE TABLE [dbo].[Table1](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] [varchar](6) NULL
) 
GO

CREATE TABLE [dbo].[Table2](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column2] [varchar](6) NULL
) 
GO

CREATE TABLE [dbo].[Table3](
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column3] [varchar](6) NULL
) 
GO

We have created three tables above in database MyTestDB, now we can drop all three table by:

DROP TABLE Table1, Table2, Table3

-- Clean up 
USE MASTER
GO
DROP DATABASE MyTestDB

SQL SERVER - View Query Editor Window In Full Screen Mode

We can view query window in full screen mode by enabling Full Screen mode.It will hide all tool windows and view only document windows. Click anywhere in the Query Editor window to activate the window.

To enable Full Screen mode:

Press ALT + SHIFT + ENTER to toggle Full Screen mode.
OR
Go to View menu >> Select Full Screen Option

To disable Full Screen mode:
Press "Full Screen" button located at top in the menu bar.

By this way we can view SQL Server Management Studio in full screen mode.

SQL SERVER - Display Line Numbers In Query Editor Window

We can display line numbers in query editor window . Line numbers are useful for navigating code in SQL Server Management Studio

Go to menu Tools >> select Options >> Click Text Editor >> Click All Languages >> Click General. Select Line numbers in Display section.

To view line numbers in only some programming languages, select Line Numbers in the appropriate folder.

It should be noted that, document will not be printed with line numbers by turning on line numbering. For line numbers to print, we must select the Include Line Numbers check box in the Print command on the File menu.

Reference: Enabling Full Screen Mode

SQL SERVER - Add New Column In Existing Table With Default Value

Many times, we need to add new column in our existing database table. Many of us prefer to use a table designer to add new column, I would like to use SQL query to do this operation. If we add new column through designer it will not update the default value for that column and we need to update that column. With the help of following query, we can add new column to our existing table with default value we want to assign to that column without executing update statement.

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

Following is the complete example to test the above SQL statement.

USE [Master]
GO
CREATE DATABASE MyTestDB
GO

USE MyTestDB
GO
CREATE TABLE [dbo].[Account](
 [AccountID] INT IDENTITY(1,1) PRIMARY KEY,
 [AccountNumber] [varchar](6) NULL,
 [BranchName] [varchar](6) NULL,
 [Balance] [numeric](8, 2) NULL
) 
GO

INSERT INTO dbo.Account(AccountNumber,BranchName,Balance)
SELECT '000001','Br001',1202.00
UNION SELECT '000002','Br002',904.00

SELECT * FROM dbo.Account

ALTER TABLE [dbo].[Account]
ADD Active BIT DEFAULT 1 WITH VALUES;

SELECT * FROM dbo.Account

DROP TABLE [dbo].[Account]

USE MASTER
GO
DROP DATABASE MyTestDB

In above example, it will add a new column ‘Active’ with default value 1 for all the rows in table ‘[dbo].[Account]'. The above example shows that we can add new column in existing table with default value in SQL SERVER using ALTER TABLE statement.

SQL SERVER - View full content of a text or varchar(MAX) column

Recently I came across with a problem where I had to debug the dynamic SQL stored in large dynamic SQL varchar datatype variable and could not select in the result grid or print the whole SQL script because of SQL server limitation of displaying maximum number of text when dealing with large dynamic SQL variables.

Therefore, I started to search the web and found one tricky solution. Here I want to share that solution for such kind of problem.

DECLARE @str AS VARCHAR(MAX)
SELECT @str = REPLICATE(CAST('A' AS VARCHAR(MAX)), 65535)

SELECT [processing-instruction(X)] = @str 
FOR XML PATH(''),TYPE

This will convert the dynamic SQL to XML, instead of printing the dynamic SQL to the message tab. XML
The column name [processing-instruction(x)] sends special XML instruction allowing the text to be converted, along with any special characters. XML also keeps the formatting and can store up to 2 GB of data. It should be noted that whatever value you put in parenthesis would be incorporated in the XML tags, in this case “X”. We can strip of the XML tags from the top and bottom of the query text and go ahead with debugging.

Reference: Allow large text to be displayed in as a link

SQL SERVER - Find Currently Running Queries

Following is the SQL script to list out which queries are currently running on SQL server database.

SELECT 
 OBJECT_NAME(t.ObjectID) as ObjectName,
 SUBSTRING(t.text,(statement_start_offset/2)+1,
  (( CASE statement_end_offset
   WHEN -1 THEN DATALENGTH(t.text)
   ELSE statement_end_offset
  END - statement_start_offset)/2) + 1
 ) AS StatementText,
 db_name(database_id) as DatabaseName,
 req.blocking_session_id as blocking_session_id,
 req.session_id,
 req.start_time,
 req.[status],
 req.command,
 req.open_transaction_count,
 req.percent_complete,
 req.cpu_time,
 req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.[sql_handle]) AS t

GO

The above query helps to identify queries that are running for long time so that an appropriate action can be taken.

more about sys.dm_exec_requests
more about sys.dm_exec_sql_text

SQL Server - Convert Comma Separated String to Table Column

In previous post I mentioned different ways to get comma separated values (CSV) from a table column.

Following are the different ways to convert comma separated string to column:

-- -- Using while loop: 

DECLARE @strCSVString VARCHAR(4000)

SELECT @strCSVString = '1,2,3,4,5'

DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

DECLARE @str VARCHAR(20)

DECLARE @idx Int

IF ISNULL(@strCSVString,'') <> ''

BEGIN

    SET @idx = CHARINDEX(',' , @strCSVString)

    WHILE @idx > 0

    BEGIN

          SET @str = SUBSTRING(@strCSVString, 1, @idx - 1)

          SET @strCSVString = SUBSTRING(@strCSVString, @idx + 1, LEN(@strCSVString) - @idx)

          INSERT INTO @Result values (@str)

          SET @idx = CHARINDEX(',' , @strCSVString)

    END

    SET @str = @strCSVString

    INSERT INTO @Result values (@str)

END

SELECT * FROM @Result 

GO

-- -- Using Common Table Expression (CTE): 

DECLARE @strCSVString VARCHAR(4000)

SELECT @strCSVString = '1,2,3,4,5'

DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

; WITH CTE(Start, [Stop]) AS

(

  SELECT  1, CHARINDEX(',' , @strCSVString )

  UNION ALL

  SELECT  [Stop] + 1, CHARINDEX(',' ,@strCSVString  , [Stop] + 1)

  FROM CTE

  WHERE [Stop] > 0

)

INSERT INTO @Result

SELECT  SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop]-Start ELSE 4000 END) AS stringValue

FROM CTE

        

SELECT * FROM @Result 

GO

-- -- Using XML: 

DECLARE @strCSVString VARCHAR(4000)

SELECT @strCSVString = '1,2,3,4,5'

DECLARE @Result TABLE(ColumnValue NVARCHAR(100))

DECLARE @xmlData XML 

SELECT @xmlData = CAST('<Col>'+ REPLACE(@strCSVString,',','</Col><Col>')+ '</Col>' AS XML)

INSERT INTO @Result            

SELECT d.value('.', 'int') AS Val

FROM @xmlData.nodes('/Col') AS x(d)

SELECT * FROM @Result 

GO

We can use the above code by creating a function and can change the delimiter string from comma to any valid character  by having a parameter in function.

For example, let's create a funtion, dbo.Split() that converts comma delimited string in to a table column.

CREATE FUNCTION [dbo].[Split]

(

@strCSVString nvarchar(2000),

@Delimiter nvarchar(5)

)  

RETURNS @Result TABLE (ColumnValue NVARCHAR(100)) 

AS  

BEGIN

  ;WITH CTE(Start, [Stop]) AS

(

 SELECT  1, CHARINDEX(@Delimiter , @strCSVString )

 UNION ALL

 SELECT  [Stop] + 1, CHARINDEX(@Delimiter, @strCSVString, [Stop] + 1)

 FROM CTE

 WHERE [Stop] > 0

)

INSERT INTO @Result

SELECT  SUBSTRING(@strCSVString , Start, CASE WHEN stop > 0 THEN [Stop] - Start ELSE 4000 END) AS StringValue

FROM CTE

 RETURN

END

GO

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

SELECT * FROM  dbo.[Split]('1,2,3,4,5' , ',')

SELECT * FROM  dbo.[Split]('1|2|3|4|5' , '|')

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

Output:

The all above queries will give the same result as: