SQL Server Script : Check SQL Server Database Status using SQL query

You can check Database Status using SQL query.

Below are the possible status of database.

• ONLINE : Database is available for access.
• RESTORING : Primary file group and secondary files are being restored.
• RECOVERING : Database is being recovered.
• RECOVERY PENDING : Resource-related error occurred during recovery.
• OFFLINE : Database is unavailable to access.
• SUSPECT : One or many primary file group is suspect and may be damaged.
• EMERGENCY : User has changed the database and set the status to EMERGENCY.

Here is query for this.

SQL Server Script : Search content or text within SQL Stored Procedures in database using SQL Query

There are many situations where in past we did some useful functionality in some stored procedures after that as time goes we forgot that how exactly we had done that functionality and now we want implement that functionality somewhere else and we do not remember where we used, but we know some important keyword or string that we used at that place. At that time this query is useful.

This query searches in all Stored Procedures in database according to given search criteria and give use the search results. In this query we are using sys.sql_modules and sys.objects system tables.

SQL Query : 

SELECT 
        so.[name] AS SP_NAME ,
        so.type_desc AS ROUTINE_TYPE,
        sm.definition AS SP_DEFINITION
FROM sys.sql_modules AS sm
INNER JOIN sys.objects AS so
    ON sm.object_id = so.object_id
WHERE sm.definition LIKE '%Quantity%'
and type='P'

Output :

(To view original image , click on image)

This is the very useful SQL Server Scripts.

Note : Give us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.

SQL Server Script : Get list of all stored procedures and it's useful details from sql server database

There are many situations where you want to list of stored procedures from your database and it's useful details like , SP created date , modify date etc.. using sql query.
There is simple way to get these information using sql query.
We are using "sys.procedures" system table to get list and details.
Here is the sql script.

SQL Server Script : Get list of all tables and it's useful details in sql server database

There are many situations where you want to list of tables and it's useful details like , table created date , modify date etc.. using sql query. There is simple way to get these informations using sql query.
We are using "sys.tables" system table to get list and details.
Here is the sql script.

SQL Query :

select * from sys.tables

Output : 

(To view original image , click on image)

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.

SQL Server Script : Get Parent Child hierarchy or tree structure or recursive records in sql query

You can get parent child hierarchy records or category sub category records or recursive records from table in sql server and display as a tree structure.
We are using A common table expression (CTE)  to get result. In fact we are using Recursive CTE to get this recursive data.

Here is example for this.
In this example we take one table "category_master". This table contains recursive records. This table has "category_id" , "category_name" and "parent_category_id" columns. "parent_category_id" column contain reference of "category_id". We can insert "N - Level" of parent child relationship records. We retrieve those records using recursive "CTE" and we can also do Hierarchy level by applying spacing so it's look like tree structure.

SQL Server Scripts : Insert particular value in Identity column in table by using IDENTITY_INSERT property

You can insert value in identity column manually using INSERT statement using "IDENTITY_INSERT" property. You need to set IDENTITY_INSERT property to ON to allow identity insert value.
If there is already IDENTITY_INSERT property ON for particular table and again you set to ON It will raise error "states SET IDENTITY_INSERT is already ON and reports the table it is set ON".
After Set ON and Insert records we need to set OFF to avoid unnecessary errors.
You need permission to set this property. By default "sysadmin" server role and the "db_owner" and "db_ddladmin" database role and the object owner has permission.

Here is example for this.
In this example we take one database table "product_master". This table has one identity column "product_id" and other column "product_name".
Now we are inserting some records and we can see that it's identity column has serial unique number. After that we delete one records from middle of that records. So now one gap introduce in serial unique number. Now if you want to insert another product it did not generate that missing serial number it will simply generate greater value from existing numbers.
Suppose we have certain situations where we want to insert that particular identity number record. So at that time we can use "IDENTITY_INSERT" property. You can see in screen shot that there is product_id="5" record is deleted and is missing from list. Now we again insert that identity value using "IDENTITY_INSERT".

SQL Query :

SET IDENTITY_INSERT [product_master] ON
INSERT INTO product_master (product_id,product_name) values(5,'USB')
SET IDENTITY_INSERT [product_master] OFF

Output (Missing Record Id Sequence) : 

Output (After inserting Missing Record Id Sequence) :

 

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.

Sql Server Scripts : Get column names of tables using inbuilt SQL Server Stored Procedure

There is a situations where you want to know the column names and it's data type and other column relevant  information of a particular table.
In SQL server there is inbuilt stored procedure "sp_columns" which returns columns information of specified table name.
Many people uses this traditional way to see column information. Like , In Microsoft SQL Server Management Studio Go to particular tables then open tables in design view mode. This way is very lengthy.
Using this inbuilt SP you can get quick result.

SQL Syntax :

sp_columns @table_name

@table_name : Specify table name.

SQL Query :

sp_columns product_master

Output :

 

(To view original image , click on image) 

Note : This output screen image has few columns information display. To View all return result information download .CSV file from here. 

Click Here To Download sp_columns_output.csv

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.

SQL Server Scripts : List all tables name that contain particular column name using SQL Query

There are many situations where you want to get all tables name that contains particular column name using sql query.

Here is query for this.
In this query we want to find column "ProductID" in all tables and get list of that.

SQL Query :

select sysobjects.name, * from syscolumns, sysobjects
where syscolumns.name='ProductID'
and sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')

Output :

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.

Sql Server Scripts : Get database name of current connection

You can get current database name using In Built SQL function "DB_NAME()".

SQL Query :

SELECT DB_NAME() AS DataBaseName

Output :

This is the very useful SQL Server Scripts.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.

Beginning SQL , SQL Server Scripts : Get XML of Table's Data using SQL Query

You can seen that XML is now every where to exchange data between multiple system and platform.
Here are sample query which retrieve XML of particular Table's data.
XML support is available on SQL Server 2000 with SQLXML 3.0 and its XML extensions, and There are in built  native XML data type support in SQLServer 2005 and 2008. 

With the help of FOR XML AUTO clause you can get XML of table's data.

Here are sample queries.

SQL Query :

SELECT * FROM product_master FOR XML AUTO 

XML AUTO returns XML fragments it does not returns a full XML document with a document element. Each row in the database becomes one element and each column in the database becomes one attribute on the element. You can see that each element in the result set is name product_master because the select clause is from product_master table.

Output XML File :

<product_master product_id="1" product_name="LCD" />
<product_master product_id="2" product_name="Processor" />
<product_master product_id="3" product_name="Cabinet" />
<product_master product_id="4" product_name="Headphone" />
<product_master product_id="5" product_name="USB" />

If you want result in Element and you also want "Product" as main element. So you have to use ELEMENT Query. Here is element Query.

SQL Query :

select * from product_master as Product FOR XML AUTO , ELEMENTS

In FOR XML AUTO , ELEMENTS return XML with a document element.

 Output XML File :

<Product>
  <product_id>1</product_id>
  <product_name>LCD</product_name>
</Product>
<Product>
  <product_id>2</product_id>
  <product_name>Processor</product_name>
</Product>
<Product>
  <product_id>3</product_id>
  <product_name>Cabinet</product_name>
</Product>
<Product>
  <product_id>4</product_id>
  <product_name>Headphone</product_name>
</Product>
<Product>
  <product_id>5</product_id>
  <product_name>USB</product_name>
</Product>

In Previous queries Return XML does not have Root Element. If you want XML structure like Root Element after that child elements.

SQL Query :

select * from product_master as Products FOR XML PATH ('Product'), TYPE, ROOT ('Products') 

In this query we are using PATH and ROOT element to make a full XML document.

Output XML File : 

<Products>
  <Product>
    <product_id>1</product_id>
    <product_name>LCD</product_name>
  </Product>
  <Product>
    <product_id>2</product_id>
    <product_name>Processor</product_name>
  </Product>
  <Product>
    <product_id>3</product_id>
    <product_name>Cabinet</product_name>
  </Product>
  <Product>
    <product_id>4</product_id>
    <product_name>Headphone</product_name>
  </Product>
  <Product>
    <product_id>5</product_id>
    <product_name>USB</product_name>
  </Product>
</Products>

In above all queries you can also use INNER JOIN , OUTER JOIN and other any type of select queries.

If you know any other way to implement this solution, Insert that in comment so I can add in this blog.

SQL Server Scripts : Insert Unicode data in SQL Server

Are you having a problem of store Unicode data in database table and it will display  "??????"  ?
Here are solution for that.
SQL Server supports Unicode data in nchar, nvarchar and ntext datatypes.
If you are inserting unicode data as regular data at that time sql does not store unicode characters it store "??????" (Question marks) in also display this "??????".
If you want to store Unicode data you need to specify 'N' ahead of data.

Here are sample example for this.
Let's we have one table "product_master" and we have one column "product_name" and it's data type "nvarchar(500)".
Now we want store one record in that without 'N' .

SQL Query without 'N'

insert into product_master(product_name) values('कंप्यूटर')
select * from product_master

Output :

Now we want store one record in that with 'N' .

SQL Query With 'N'

insert into product_master(product_name) values(N'कंप्यूटर')
select * from product_master

Output :

 

Sql server scripts : Get SQL Server Database Physical file Location and file size using sql script

Some time you Database Physical file Address and file size using sql script.

SQL Script :

SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name,(size*8)/1024 Size_MB
FROM sys.master_files

You can also query for particular database name like :

SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name,(size*8)/1024 Size_MB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'TempDatabase'

Output :

(To view original image , click on image)

Sql Server : How to Reset the Identity Column of a table in Sql Server

Sometimes we have situation in which we want delete all records of table and that table containts identity column as primary key and we want again insert some new records in this table ,
so in this case identity column does not start with 1 but it start with last number that we had before deleted records.
To avoid this situation we need to reset identity column.
After reset identity column it will start with 1.

Here are sample example for that :

We have one table "product_master" , in this table we have one identity column "product_id".
Now we inserted 3 records into this , so the table entries look like this :

After Delete all records using this query :

     delete from product_master

Now table is empty.

After delete now if we insert new record. The newly added record have product_id value 4. because identity column does not reset.You can see this in below image

To reset Identity column Here are Syntax:
Syntax :

    DBCC CHECKIDENT('TableName', RESEED, 0)

Now again delete all records and execute this command :

    DBCC CHECKIDENT('product_master', RESEED, 0)

This command resent the identity of given table.

Now again you insert one record this record has product_id value 1. You can see this in below image.

FDTBBTJNWV8H

SQL Server : Comma seperated values from table column - Multiple Solutions

In some cases we need to get comma separated or any user defined separator values from table column.
There are multiple solutions for that.

Here is example :
Create one temp table and insert some records.
This table is two column one sport_name and one identity column.

    Create table #TestSport (sport_name varchar(max), id int not null identity) on [Primary]
    GO
    INSERT INTO #TestSport (sport_name) VALUES ('cricket')
    INSERT INTO #TestSport (sport_name) VALUES ('hockey')
    INSERT INTO #TestSport (sport_name) VALUES ('football')
    INSERT INTO #TestSport (sport_name) VALUES ('baseball')
   
    GO

Now if we want comma seperated value of sport_name columns.
Here are some scripts.

-- Get CSV values using SUBSTRING Function

SELECT SUBSTRING(
(SELECT ',' + s.sport_name
FROM #TestSport s
ORDER BY s.sport_name
FOR XML PATH('')),2,200000) AS CSV_USING_SUBSTRING

Output : 

-- Get CSV values Using STUFF Function

SELECT STUFF(
(SELECT ',' + s.sport_name
FROM #TestSport s
ORDER BY s.sport_name
FOR XML PATH('')),1,1,'') AS CSV_USING_STUFF

Output : 

-- Get CSV values Using COALESCE Function

DECLARE @Csv varchar(Max)
SELECT @Csv= COALESCE(@Csv + ',', '') +
CAST(s.sport_name AS varchar(50))
FROM #TestSport s
ORDER BY s.sport_name
SELECT @Csv as CSV_USING_COALESCE

Output : 

Quiz : What is name the .NET Framework feature that can be used to add a method to pre-compiled classes without using inheritance ?

What is name the .NET Framework feature that can be used to add a method to pre-compiled classes without using inheritance  ?

        A. Expression trees
        B. Lambda Expression
        C. Anonymous methods
        D. Extension methods

Please give your answer in comment.

Note : Correct answer will be after one week.

Beginning SQL : Delete duplicate records from table

There are a situation in which you want to delete duplicate records from table.

Here are example in which i created on temp table and add four columns,
in this three columns are data columns and one column is ID column.

There is a situation in which duplicate records are inserted in tables only ID column data is unique and other columns data are repeated
in this case you can use following query :

Create Temp table and insert some dummy records :

Create table #Test (colA int not null, colB int not null, colC int not null, id int not null identity) on [Primary]
GO
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)
INSERT INTO #Test (colA,colB,colC) VALUES (1,1,1)

INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)
INSERT INTO #Test (colA,colB,colC) VALUES (1,2,3)

INSERT INTO #Test (colA,colB,colC) VALUES (4,5,6)
GO

Before Execute delete query table data looks like this :

Select * from #Test
GO

Output : 

Here are query to delete duplicate records :

Delete from #Test where id <
(Select Max(id) from #Test t where #Test.colA = t.colA and #Test.colB = t.colB and #Test.colC = t.colC)
GO

After Execute delete query table data looks like this :

Select * from #Test
GO

Output : 

This query is really very useful at a time of data migration.

Beginning SQL : Difference Between Local and Global temporary table in SQL SERVER

Local temporary tables are visible only in to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced.
Local temporary tables are deleted after disconnects from SQL Server instance .
Global temporary tables are visible to any user and any connection after they are created,
and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

You can create tables using following syntax.

Table Variable :

          DECLARE @tmp TABLE

Table variables are only visible to the the connection that creates it, are stored in RAM,
and are deleted after the batch or stored procedure ends.

Local temporary tables :

          CREATE TABLE #tmp

Local temporary tables are only visible to the connection that creates it,
and are deleted after the connection is closed.

Global temporary tables :

          CREATE TABLE ##tmp

Global temporary tables are visible to everyone, and are deleted after the connection that created it is closed.

Tempdb permanent tables :

          USE tempdb CREATE TABLE tmp

Tempdb permanent tables are visible to everyone, and are deleted when the server is restarted.

Local Table can not be shared between multiple users.
Global Table can be shared between multiple users.

Write in comment if you know more differences. I will add this in my blog.

Create a FILESTREAM-Enabled Database

If you want to use FILESTREAM data storage features in database you must create FILESTREAM enabled database.
Must specify the CONTAINS FILESTREAM clause for at least one filegroup.

Here are the sample script to create FILESTREAM-Enabled Database :

CREATE DATABASE AccountSystem
ON
PRIMARY ( NAME = accountsystem1,
    FILENAME = 'c:\data\accountsystemdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = accountsystem3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\accountsystemlog1.ldf')
GO

Here this script Create Database name "AccountSystem".
This database contains three filegroups PRIMARY ,  accountsystem1 AND FileStreamGroup1.
PRIMARY  and accountsystem1 are regular file groups that cannot contain FILESTREAM data.
FileStreamGroup1 is the FILESTREAM filegroup.

For a FILESTREAM filegroup, FILENAME refers to a path. The path up to the last folder must exist, and the last folder must not exist.
In this example, c:\data must exist. However, the filestream1 subfolder cannot exist when you execute the CREATE DATABASE statement.

After you run this script, a filestream.hdr file and an $FSLOG folder appears in the c:\Data\filestream1 folder.
The filestream.hdr file is a header file for the FILESTREAM container.

Important

The filestream.hdr file is an important system file. It contains FILESTREAM header information. Do not remove or modify this file.

You can use the ALTER DATABASE statement to add a FILESTREAM filegroup for an exsiting database.

New FILESTREAM Data Storage Features in SQL Server 2008 : Part 1

FILESTREAM storage is use to store unstructured data, such as documents and images, on the file system.
Before we can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine.
Here are describes how to enable FILESTREAM by using SQL Server Configuration Manager.

Important Note :

You cannot enable FILESTREAM on a 32-bit version of SQL Server running on a 64-bit operating system.

Step enable and change FILESTREAM settings

1. Open SQL Server Configuration Manager from Microsoft SQL Server 2008 R2 's  Configuration Tools Menu  .
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.
10. In SQL Server Management Studio, click New Query to display the Query Editor.
11. In Query Editor, enter the following Transact-SQL code:
    
    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE
12. Click Execute.
13. Restart the SQL Server service.

Sql Server Beginners : Various functions to get system date and time in Sql Server 2008

Here are varios functions to get system date and time.

SELECT SYSDATETIME() AS 'SYSDATETIME'
Output :-

2012-03-09 12:01:17.25

SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'
Output :-

2012-03-09 12:01:17.2580000 +00:00

SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'
Output :-

2012-03-09 12:01:17.25

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'
Output :-

2012-03-09 12:01:17.257

SELECT GETDATE() AS 'GETDATE'
Output :-

2012-03-09 12:01:17.257

SELECT GETUTCDATE() AS 'GETUTCDATE'
Output :-

2012-03-09 12:01:17.257