Identity field in MSSQL

Identity fields are auto incremented field. There can be only one identity field per table. It needs the base value and the increment value with which it will be incremented whenever new record is inserted in the table. An IDENTITY column of tinyint datatype can go upto 255, smallint can go upto 32767, int can go upto 2147483647 and bigint can go upto 9223372036854775807.

CREATE TABLE dbo.Employee ( EmployeeID smallint identity(7,2), EmployeeName char(20) )

Now we need to just enter the name of the employee not his id.

Insert into table dbo.Employee(EmployeeName) Values(“Kshyup”)

After insertion and deletion of data we may be in need of resetting the identity column counter for that we can make use of DBCC CHECKIDENT. For example

DBCC CHECKIDENT(‘Employee’, RESEED, 1)

Getting backup file information before restoring

Some time we way find a situtation where we need to get the information about the backup file or device before restoring that backup for example we may want the file it is containing or the media information for that purpose we can use the following command

To get the information  regarding the file it contains

Restore FilelistOnly From Disk = N'C:\Sample.Bak'

This will return the information about the file which is there in the backup and if you want to see the labels only then you can use

Restore LabelOnly From Disk = N'C:\Sample.Bak'

Other useful arguments can be REWINDONLY and VERFIYONLY. For more details on this visit MSDN

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

Generating Alter Query on the basis of the foreign keys present in the database

We can utilize the query of listing foreign key with respective table and fields to generate the alter query for adding constraint. For that we will use the XML and temp table and will generate the list of the alter queries

SELECT TABS.NAME AS [REFTABLE],
   (SELECT NAME
       FROM SYS.TABLES AS CTAB
       WHERE (OBJECT_ID = FK.PARENT_OBJECT_ID)) AS [PARENTTABLE], 
          FK.NAME AS [KEYNAME],
          AC.NAME AS [COLUMNNAME] INTO #TEMPTABLE
FROM SYS.TABLES AS TABS INNER JOIN             
SYS.FOREIGN_KEYS AS FK ON FK.REFERENCED_OBJECT_ID = TABS.OBJECT_ID INNER JOIN             
SYS.FOREIGN_KEY_COLUMNS AS FKC ON FK.OBJECT_ID = FKC.CONSTRAINT_OBJECT_ID INNER JOIN             
SYS.ALL_COLUMNS AS AC ON AC.COLUMN_ID = FKC.PARENT_COLUMN_ID
		     AND AC.OBJECT_ID = FKC.PARENT_OBJECT_ID

SELECT 'ALTER TABLE ' + [PARENTTABLE] + ' ADD CONSTRAINT ' + 
	[KEYNAME] + ' FOREIGN KEY (' + REPLACE((SELECT 
[COLUMNNAME]  AS 'data()' FROM  #TEMPTABLE AS TT 
WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] FOR XML PATH('')),' ',',')
+ ') REFERENCES (' + REPLACE((SELECT [COLUMNNAME]  AS 'data()' 
FROM  #TEMPTABLE AS TT WHERE TT.[KEYNAME] = #TEMPTABLE.[KEYNAME] 
FOR XML PATH('')),' ',',') + ') ON UPDATE CASCADE ON DELETE CASCADE'    
FROM 
	#TEMPTABLE GROUP BY [KEYNAME], [PARENTTABLE], [REFTABLE]

DROP TABLE #TEMPTABLE

Getting List of all the foreign keys and there details with table and field name

To list the all the foreign keys available in database and there associated tables and fields we can utilize following query

SELECT tabs.name AS [Reference Table],
   (SELECT name FROM sys.tables AS ctab
       WHERE (object_id = fk.parent_object_id)) AS [Parent Table], 
       fk.name AS [Key Name],
        ac.name AS [Column Name] 
FROM sys.tables AS tabs INNER JOIN             
sys.foreign_keys AS fk ON fk.referenced_object_id = tabs.object_id inner join             
sys.foreign_key_columns as fkc on fk.object_id = fkc.constraint_object_id inner join             
sys.all_columns as ac on ac.column_id = fkc.parent_column_id
		     and ac.object_id = fkc.parent_object_id 
ORDER BY tabs.name, 2

Linking another SQL Server instance Part 2 Single query for two server instance

In the part 1 we added one server as linked server to another instance of the mssql server now we will right a sample query which will retrun the result from both the server tables and will give single output

select * from master.sys.objects
union
select * from [inventory].master.sys.objects

it will return the result after combining the result of both the databases of different instance. So you will get the list of objects which are there in both the instances. Now we will take another sample in which we will have the product database in one instance and customer database in another instance we will join them on one field and will get the result after joining them

select productname, productid, customername, customerid from products.dbo.productlist as prolist
	inner join [inventory].customers.dbo.customerlist as cuslist on (cuslist.productid = prolist.productid)

here result from the one instance and customer data is coming from the inventory server

Linking another SQL Server instance Part 1

There can be case or a requirement to attach the another sql server to your existing sql server instance so that you can work on the both server at same time. Get data from both the server from one query as a single result, managing all server from one location or for various work you can utilise it.

Linking another server

for that we have to use sp_addlinkedserver. one of the example

 
EXEC sp_addlinkedserver   
@server='inventory', 
@srvproduct='',
   @provider='SQLNCLI', 
@datasrc='server\mssql2005'

for more information on this visit sp_addlinkedserver (Transact-SQL) – MSDN

Here we added the server\mssql2005 as the inventory server as linked server now we have to add the login information for that we have to use

 
EXEC sp_addlinkedsrvlogin 'inventory', 
'false', 'mydomain\user2', 
'sa', 'sapassword

To get more information on this checkout sp_addlinkedsrvlogin (Transact-SQL) – MSDN

Now that server is linked and ready to be user. we can use the following query to get the data from the server

 
SELECT * FROM [inventory].master.dbo.sysobjects

Listing SQL Server Instances running in network with details C#

To list the server running in the listbox or the grid we can use following code and give user a option to select the server to whom he wants to connect

using System;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;

namespace ListSQLServer
{
public partial class serverListing : Form
{
public serverListing()
{
InitializeComponent();
}

private void listButton_Click(object sender, EventArgs e)
{
DataTable dtServer = SqlDataSourceEnumerator.Instance.GetDataSources();
serverDetailsGrid.DataSource = dtServer;
}
}
}


you will get the following result in the grid

List of servers on network

Spawning a Windows command shell through MS SQL Server

Most of time you will find certain tasks to be done which can be done by executing commands from the dos prompt of windows. For example you want to delete certain data files which are dettached from the server or for any other work. For that we need to have access to the server where we can open a command prompt and execute the commands.

But there is a smart way to do it from the sql server also with the help of the xp_cmdshell stored procedure. This procedires spwans the windows command shell and we can exucte neumrous commands through it which will be retured back to us as a result in the tabular format

xp_cmdshell { 'command_string' } [ , no_output ]

It can be used for various requirement. One very good example of it is given at
http://blog.sqlauthority.com/2007/04/13/sql-server-script-to-find-sql-server-on-network/

which tells how to get the list of the sql server running on the network by executing the stored procedure for more information on xp_cmdshell please visit following link 

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

if xm_cmdshell is disabled then first we have to enable it by executing following commands

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE

but enabling it on server is risky since from network user can excute commands on the server which normally he cann’t do. So some risk are involved. which can be handled by the rights

Check who all are connected to SQL Server

To check who all are connected to sql server and what they are doing, is a very important requirement. It can be required when you want to perform some exclusive work on you database(s) or for some other work also. In order to check who all are there and connected to which database we can use following stored procedure.

–Check who all connected and doing what
exec sp_who
go

It will give you the following result 

 

Result of store procedure sp_who

Getting sql query output in XML

Most of the time we will be executing sql queries and getting results as number of rows mainly records. In order to get those records in the XML format we can modify our existing query by adding just few keywords. In the result of those queries we will get the result in raw or formatted XML. 

Normal query 

Select * from customers

Result of select query

Query for getting result in XML format

Select * from customer FOR XML RAW or

Result of query with

Select * from custoemr FOR XML AUTO

Result of query

Now we will get the result in well formed xml by getting the elements well tagged and root node as custoemr with following query

Select * from customer FOR XML AUTO, ELEMENTS, ROOT(‘customer’)

Result of Query