Search

Loading...

Saturday, March 31, 2012

List tables which are dependent on a given table

Option 1: Right-click on a table and choose 'View Dependencies'.

Option 2: For some reason if you want to do it programmatically check out the below code snippet

Select
S.[name] as 'Dependent_Tables'
From
sys.objects S inner join sys.sysreferences R 
on S.object_id = R.rkeyid
Where S.[type] = 'U' AND R.fkeyid = OBJECT_ID('Person.StateProvince')
 

Friday, March 30, 2012

Reclaiming the table space after dropping a column [without clustered index]

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a non-clustered Index.


Create a table with non-clustered index in it:

Create Table tblDemoTable_nonclustered (
[Sno] int primary key nonclustered,
[Remarks] varchar(5000) not null
)
Go


Pump-in some data into the newly created table:

Set nocount on
Declare @intRecNum int
Set @intRecNum = 1

While @intRecNum <= 15000 

Begin 
Insert tblDemoTable_nonclustered (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109)) 
Set @intRecNum = @intRecNum + 1 
End 

Check the fragmentation info before dropping the column:

DBCC SHOWCONTIG ('dbo.tblDemoTable_nonclustered')
GO

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table...
Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 84
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 91.67% [11:12]
- Extent Scan Fragmentation ...................: 41.67%
- Avg. Bytes Free per Page.....................: 417.4
- Avg. Page Density (full).....................: 94.84%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Drop the Remarks column and reindex the table:

Alter table tblDemoTable_nonclustered drop column Remarks
go

DBCC DBREINDEX ( 'dbo.tblDemoTable_nonclustered' )
Go

Now check the Fragmentation info:

DBCC SHOWCONTIG ('dbo.tblDemoTable_nonclustered')
GO

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table...
Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 84
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 91.67% [11:12]
- Extent Scan Fragmentation ...................: 41.67%
- Avg. Bytes Free per Page.....................: 417.4
- Avg. Page Density (full).....................: 94.84%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you see there won't be any difference or rather the space hasn't reclaimed yet. 
Here, DBCC DBREINDEX won't work as nonclustered index are stored in heap. i.e., Heaps are tables that have no clustered index.

Solution:

Select * into #temp from tblDemoTable_nonclustered
Go

Truncate table tblDemoTable_nonclustered
Go

Insert into tblDemoTable_nonclustered select * from #temp
Go

Now check the fragmentation info to see that we have actually reclaimed the space!

DBCC SHOWCONTIG scanning 'tblDemoTable_nonclustered' table...
Table: 'tblDemoTable_nonclustered' (1781581385); index ID: 0, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 25
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 3.6
- Scan Density [Best Count:Actual Count].......: 57.14% [4:7]
- Extent Scan Fragmentation ...................: 57.14%
- Avg. Bytes Free per Page.....................: 296.0
- Avg. Page Density (full).....................: 96.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Thursday, March 29, 2012

Get File Date

This procedure gets the date a file was updated, as reported by the file system. 
exec sp_configure 'show advanced options', 1;  

reconfigure;  
exec sp_configure 'xp_cmdshell',1;  
reconfigure;  
go  
  
use master  
go  
  
create procedure [dbo].[get_file_date](  
@file_name varchar(max)  
,@file_date datetime output  
) AS   
BEGIN   
set dateformat dmy  
declare @dir table(id int identity primary key, dl varchar(2555))  
declare @cmd_name varchar(8000),@fdate datetime,@fsize bigint, @fn varchar(255)  
set @fn=right(@file_name,charindex('\',reverse(@file_name))-1)  
set @cmd_name='dir /-C "'+@file_name+'"'  
  
insert @dir  
exec master..xp_cmdshell @cmd_name  
  
select @file_date=convert(datetime,ltrim(left(dl,charindex('   ',dl))),103)   
from @dir where dl like '%'+@fn+'%'  
  
end  
go  


Usage:


declare @file_date_op datetime   
exec master.dbo.get_file_date   
  @file_name = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\MSDBData.mdf'  
 ,@file_date = @file_date_op OUTPUT  
  
SELECT @file_date_op 

Wednesday, March 28, 2012

Move Database from one disk to other

Method 1
  • Detach the database
  • Move the file to other disk 
  • Attach the DB
Method 2
  • Backup the database
  • Drop the database
  • Restore database specifying location of data and log file to other disk.

Tuesday, March 27, 2012

Difference between two dates in SQL Server

If you want to get the difference between two dates in SQL Server then you need to make use of DATEDIFF function. 

DECLARE @StartDate DATETIME
SET @StartDate = '2012-03-08 12:22:00.000'

Declare @EndDate DATETIME
SET @EndDate = 
'2012-03-12 11:32:00.000' 

--To get only Hours
SELECT DATEDIFF(hh, @StartDate,@EndDate ) AS [Hours];

--To get the result in Minutes
SELECT CAST(DATEDIFF(ss, @StartDate, @EndDate) AS DECIMAL(10, 0)) / 60 AS [Minutes];

--To get both Hours and Minutes
SELECT DATEDIFF(hh, @StartDate, @EndDate) AS [Hours],
DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, @StartDate, @EndDate),@StartDate),@EndDate) AS [Minutes]; 

Monday, March 26, 2012

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

1. Within SQL Server we can store DATETIME values between 01/01/1753 and 12/31/9999 only. If we try to enter a value which isn't this range then it would throw an error.


3. The way you handle dates will depend on the DATEFORMAT set for your login. Internally DATEFORMAT takes its value from SET LANGUAGE.


/*
This code block would throw an error
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
*/
SET DATEFORMAT MDY
GO


DECLARE @dt VARCHAR(20)


--Format is Month Day Year. But we are passing 13 as month so it is expected to throw the error
SET @dt = '13/19/11 12:33:12'


SELECT CONVERT(DATETIME, @DT)
GO


--Now it will work!
SET DATEFORMAT DMY
GO


Declare @dt VARCHAR(20)
SET @dt =   '13/19/11 12:33:12' 


SELECT CONVERT(DATETIME, @DT) 

Saturday, March 24, 2012

Find missing identity value in a table

Follow below procedure to find the missing identity values in a table:


Example



Declare @intMaxNum int
Select @intMaxNum = max(AccountID) from AccountMaster;


With tempData (result) as
(
Select distinct FG.AccountID + 1 from AccountMaster FG where not exists
(
Select 1 from AccountMaster FGP where FGP.AccountID = FG.AccountID + 1
) and FG.AccountID < @intMaxNum


Union All


Select TD.result + 1 from tempData TD where not exists
(
Select 1 from AccountMaster FGP where FGP.AccountID = TD.result + 1
) and TD.result < @intMaxNum
)
Select result as 'Missing Numbers' from tempData order by result;

Friday, March 23, 2012

Show all triggers in a database with source code

Run below query to list all triggers in a database with source code:


SELECT 
Object_name(T.parent_id) AS [Table Name],
T.[Name] AS [Trigger Name],
T.is_disabled AS [isDisabled],
T.is_Instead_of_trigger AS [isInsteadOfTrigger],
M.definition AS [Source Code], 
T.create_date AS [Created on],
T.modify_date AS [Modified on]
FROM 
SYS.TRIGGERS T INNER JOIN SYS.SQL_MODULES M 
ON T.Object_id = M.Object_ID
WHERE 
T.[Type] = 'TR' AND
T.is_ms_shipped = 0 AND
T.[parent_id] > 0
ORDER BY 
Object_name(T.parent_id), T.[Name]
GO

Thursday, March 22, 2012

Reclaiming the table space after dropping a column - [With Clustered Index]

If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a clustered Index.

Create a table with clustered index in it:

Create Table tblDemoTable (
[Sno] int primary key clustered,
[Remarks] char(5000) not null
)
Go

Pump-in some data into the newly created table:

Set nocount on
Declare @intRecNum int
Set @intRecNum = 1

While @intRecNum <= 15000 

Begin 
Insert tblDemoTable (Sno, Remarks ) Values (@intRecNum, convert(varchar,getdate(),109)) 
Set @intRecNum = @intRecNum + 1 
End

If it's SQL 2000 or earlier:

DBCC SHOWCONTIG ('dbo.tblDemoTable') -- Displays fragmentation information for the data and indexes of the specified table 
Go

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable' table...
Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 80 
- Extents Scanned..............................: 12
- Extent Switches..............................: 11
- Avg. Pages per Extent........................: 6.7
- Scan Density [Best Count:Actual Count].......: 83.33% [10:12]
- Logical Scan Fragmentation ..................: 3.75%
- Extent Scan Fragmentation ...................: 8.33%
- Avg. Bytes Free per Page.....................: 33.7
- Avg. Page Density (full).....................: 99.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If you are using SQL 2005:

As that DBCC feature would be removed in the future version of SQL Server I would suggest the following code snippet instead of DBCC SHOWCONTIG.

Declare @object_id int;
Set @object_id = Object_ID(N'Testbed.dbo.tblDemoTable');

Begin
Select index_type_desc, index_depth, index_level,
avg_fragmentation_in_percent, avg_fragment_size_in_pages,
page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (Db_id(), @object_id, NULL, NULL , 'Detailed');
End
Go

Output:


Drop the column 'Remarks' from the table:

Alter table tblDemoTable drop column Remarks
Go 


Now try out DBCC SHOWCONTIG or sys.dm_db_index_physical_stats as explained previously and verify that the details haven't changed a bit :)

Solution:

DBCC DBREINDEX ( 'dbo.tblDemoTable' )
Go

Now try out either the SHOWCONTIG or dm_db_index_physical_stats and see that you have reclaimed the space successfully. 

DBCC SHOWCONTIG ('dbo.tblDemoTable')
Go

Output:

DBCC SHOWCONTIG scanning 'tblDemoTable' table...
Table: 'tblDemoTable' (1717581157); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned................................: 25 
- Extents Scanned..............................: 5
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 5.0
- Scan Density [Best Count:Actual Count].......: 80.00% [4:5]
- Logical Scan Fragmentation ..................: 8.00%
- Extent Scan Fragmentation ...................: 20.00%
- Avg. Bytes Free per Page.....................: 296.0
- Avg. Page Density (full).....................: 96.34%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

May be this is one another good example of why we need to have clustered index on a table :)

Similarly if you have run this DMV sys.dm_db_index_physical_stats then the output would be this:



BTW, in SQL 2005 though DBCC DBREINDEX would work, its better to start practicing ALTER INDEX syntax.

In the next post we would see how to reclaim the space in a table which doesn't have clustered index in it.

Wednesday, March 21, 2012

SELECT column alias can be used in ORDER BY but not in GROUP BY clause


We can use SELECT column alias in ORDER BY clause. But we can't use in GROUP BY clause.


SQL is implemented as if a query was executed in the following order:
  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause
This order in most relational database systems explains which names (columns or aliases) are valid because they must have been introduced in a previous step. There are exceptions though.
You could always use a subquery so you can use the alias 
SQL Server doesn't allow you to reference the alias in the GROUP BY clause because of the logical order of processing. The GROUP BY clause is processed before the SELECT clause, so the alias is not known when the GROUP BY clause is evaluated. This also explains why you can use the alias in the ORDER BY clause. 
In Short,
ORDER BY clause is processed after the SELECT statement; 
GROUP BY clause is processed before the SELECT statement.


Tuesday, March 20, 2012

Find out recently run queries

With the help of a DMV (Dynamic Management Views) and a table valued function we can list the required result.

Notes

  • sys.dm_exec_sql_text -- This table valued function returns the text of the SQL batch that is identified by the specified sql_handle.
  • sys.dm_exec_query_stats -- This DMV returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
Solution:
Select
dmStats.last_execution_time as 'Last Executed Time',
dmText.text as 'Executed Query'
from
sys.dm_exec_query_stats as dmStats
Cross apply
sys.dm_exec_sql_text(dmStats.sql_handle) as dmText
Order By
dmStats.last_execution_time desc

Monday, March 19, 2012

Delete all backup files older than specified days.

Use below script to delete all backup files older than specified days.


Option Explicit
on error resume next
Dim intDaysOld
Dim strDirectoryPath
Dim objFSO
Dim objFolder
Dim objFileCollection
Dim objFile


'Customize values here to fit your needs
intDaysOld = 3
Set objFSO = CreateObject("Scripting.FileSystemObject")
strDirectoryPath = "\\Backup\SQL_Server\"
set objFolder = objFSO.GetFolder(strDirectoryPath)
set objFileCollection = objFolder.Files


'Walk through each file in this folder collection.
For each objFile in objFileCollection
If objFile.DateLastModified < (Date() - intDaysOld) Then
objFile.Delete(True)
End If
Next


'Clean up
Set objFSO = Nothing
Set objFolder = Nothing
Set objFileCollection = Nothing
Set objFile = Nothing

Saturday, March 17, 2012

Script to backup all database in a Server

Use below script to take backup of all Database in a SQL Server:



Set Nocount on
Declare @Database_Name varchar(100)
Declare @Server_Name varchar(100)
declare @d varchar(30)


Declare Cur_DB Cursor For
        -- ALL USER DATABASES
        Select name from master.dbo.sysdatabases
        where dbid > 4 


Declare @osql varchar(1000)


select @Server_Name = @@Server_Name
select @d = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30), GETDATE(), 120), '-', ''), ':', ''), ' ', '_')


Open Cur_DB
Fetch Next from Cur_DB into @Database_Name
While @@Fetch_status=0
    Begin
        Set @osql='EXEC master.dbo.xp_cmdshell '+''''+'sqlcmd -E -S '+@Server_Name+' -Q"BACKUP DATABASE ['+@Database_Name+'] TO disk = ''''\\filer\SQL_BACKUPS_SHARE\myserver\UserDBs\'+@Database_Name+'_' + @d + '.bak'''' " -o"C:\SQLLogs\Agent Jobs\Backup User Databases - '+@Database_Name+'.log"'+''''
        EXEC (@osql) --Execute the osql statement
        Fetch Next from Cur_DB into @Database_Name
    End
Close Cur_DB


Deallocate Cur_DB

Friday, March 16, 2012

Cannot detach a suspect or recovery pending database

Today once the connectivity to the data and log volume for SQL servers is lost and I received the following error:


Log Writer: Operating system error 2
(failed to retrieve text for this error. Reason: 15100) encountered.


This resulted in the database on that volume showing as being suspect.
I tried to detach the database, after connecting back to the Server, but got below error:


Cannot detach a suspect or recovery pending database.
It must be repaired or dropped. (Microsoft SQL Server, Error: 3707) 


To resolve above error i did the following:
ALTER DATABASE DemoData SET EMERGENCY;
GO


EMERGENCY mode sets the database as READ_ONLY, disabled logging, and access is limited to sysadmins. Marking the database in this mode is a first step for resolving log corruption.


ALTER DATABASE  DemoData set single_user
GO
DBCC CHECKDB ( DemoData, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE  DemoData set multi_user
GO


This should resolve any corruption and bring the database online. The database will come out of EMEREGENCY mode automatically.

Thursday, March 15, 2012

Run CheckDB on all Database

Use below script to run CheckDB on all the Database on Server.


Set Nocount on
Declare @Server_Name varchar(65) 
Declare @Database_Name varchar(65)


Declare Cur_DB Cursor For
        Select name from master.dbo.sysdatabases


Declare @osql varchar(2000)


select @Server_Name = @@Server_Name


Open Cur_DB
Fetch Next from Cur_DB into @Database_Name
While @@Fetch_status=0
    Begin
        Set @osql='DBCC Checkdb (['+@Database_Name+']) WITH PHYSICAL_ONLY'
        EXEC (@osql) --Execute the osql statement
        Fetch Next from Cur_DB into @Database_Name
    End
Close Cur_DB


Deallocate Cur_DB


GO

Wednesday, March 14, 2012

Avoid using INSERT without a column list

How to avoid using INSERT without a column list?
It is undesirable to include insert TableX values(...) in your code or stored procedure because if a column is added or deleted, you can expect a sure bug.


With sp_help TableX you can easily get a column list. Just cut and paste it, add commas and you have the insert with the explicit column names.


You can also do this from the Object Explorer in SSMS via the Script Table option.

Tuesday, March 13, 2012

How to check what service pack is installed

Use the following code in SSMS Query Editor to get version, edition and Service Pack information:


USE master;

SELECT @@Version
/*
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:27:29  
Copyright (c) 1988-2008 Microsoft Corporation 
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )
*/
SELECT SERVERPROPERTY('ProductLevel')
-- SP1

Monday, March 12, 2012

set the default language & default database for a login

Use below query to change default language:


-- Setting default language for login
use master

EXEC sp_defaultlanguage 'Deutsch', 'us_english'
GO

-- SQL Server 2005 and onward
ALTER LOGIN kimberlyhay WITH DEFAULT_LANGUAGE = British;
GO

-- Get languages information
EXEC sp_helplanguage
------------

-- Languages in SQL Server 2008 with dateformat
SELECT LanguageID = langid,
       name,
       alias,
       dateformat
FROM     sys.syslanguages
ORDER BY langid
GO


Use below query to change default Database:

-- Setting default database for login
EXEC sp_defaultdb ' Deutsch ', 'AdventureWorks'

-- SQL Server 2005 and SQL Server 2008
ALTER LOGIN kimberlyhay WITH DEFAULT_DATABASE = AdventureWorks;
------------

Saturday, March 10, 2012

How to minimize Deadlock

Deadlocks can be prevented by one or more of the following methods:
  • Adding missing indexes to support faster queries
  • Dropping unnecessary indexes which may slow down INSERTs for example
  • Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)
  • Adding index hints to queries
  • Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int
  • Modifying the stored procedures to access tables in a similar pattern
  • Keeping transactions as short and quick as possible: "mean & lean"
  • Removing unnecessary extra activity from the transactions like triggers
  • Removing JOINs to Linked Server (remote) tables
  • Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables
  • Setting MAXDOP=1 solves deadlocking in some cases

Friday, March 9, 2012

Generate running total

Use below query to generate running total:


Method 1

/*
 SQL Server Running Total Calculation
*/
DECLARE @DateStart date='2004-01-01'
;WITH CTE
     AS (SELECT ID = ROW_NUMBER() OVER(ORDER BY PurchaseOrderID),
                PurchaseOrderID, OrderDate = CONVERT(DATE,OrderDate),SubTotal
         FROM   AdventureWorks2008.Purchasing.PurchaseOrderHeader
         WHERE  OrderDate >= @DateStart)
SELECT   PurchaseOrderID,  OrderDate, SubTotal,
         RunningTotal = (SELECT SUM(SubTotal)
                         FROM   CTE
                         WHERE  ID <= A.ID)
FROM     CTE AS A
ORDER BY ID


Method 2

DECLARE @SalesTbl TABLE (DayCount smallint, Sales money, RunningTotal money)

DECLARE @RunningTotal money

SET @RunningTotal = 0

INSERT INTO @SalesTbl 
SELECT DayCount, Sales, null
FROM Sales
ORDER BY DayCount

UPDATE @SalesTbl
SET @RunningTotal = RunningTotal = @RunningTotal + Sales
FROM @SalesTbl

SELECT * FROM @SalesTbl



Wednesday, March 7, 2012

Find the File Size, last modified date

Use the below procedure to find the required information from file.


CREATE PROCEDURE List_File_Info
AS
SET NOCOUNT ON

DECLARE @SQL VARCHAR(500), @FName VARCHAR(40), @Error INT
DECLARE @Msg VARCHAR(100), @Return INT
DECLARE @FileDate VARCHAR(20), @FileSize VARCHAR(10)

SET DATEFORMAT MDY

IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL DROP TABLE #DirList

CREATE TABLE #dirlist (FName VARCHAR(1000))
CREATE TABLE #Errors (Results VARCHAR(1000))

-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec master..xp_cmdshell 'dir /OD C:\*.*'

SET @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @Msg = 'Got error while getting the File Names with DIR '
GOTO On_Error
END

-- Remove the garbage
DELETE #dirlist WHERE SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR FName IS NULL

--SELECT * FROM #DirList
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName, SUBSTRING(FName,1,20) AS
FileDate, SUBSTRING(FName,30,10) AS FileSize
FROM #dirlist WHERE FName NOT LIKE '%<DIR>%'

OPEN curDir

FETCH NEXT FROM curDir INTO @Fname,@FileDate,@FileSize
WHILE (@@fetch_status = 0)
BEGIN
Print @FName + ' ' + @FileDate + ' ' + @FileSize
FETCH NEXT FROM curDir INTO @Fname,@FileDate,@FileSize
END

CLOSE curDir
DEALLOCATE curDir

DROP TABLE #DirList
DROP TABLE #Errors

RETURN @Error

On_Error:
BEGIN
IF @Error <> 0
BEGIN
SELECT @Msg + '. Error # ' + CAST(@Error AS VARCHAR(10))
RAISERROR(@Msg,12,1)
RETURN @Error
END
END
GO




Tuesday, March 6, 2012

select from xml using nodes() function

Check below script:


DECLARE @myXml AS XML
SET @myXml = 
'<ArrayOfCustomer>
        <Customer id="1">
                <Description>Mike</Description>
        </Customer>
        <Customer id="2">
                <Description>James</Description>
        </Customer>      
        <Customer id="3">
                <Description>Brian</Description>
        </Customer>      
        <Customer id="4">
                <Description>Taylor</Description>
        </Customer>      
</ArrayOfCustomer>'


SELECT
        T.c.value('@id[1]', 'int') AS [id]
        , T.c.value('Description[1]', 'nvarchar(max)') AS [description]
FROM @myXml.nodes('/ArrayOfCustomer/Customer') AS T(c)

Monday, March 5, 2012

List all tables with size and row count

Use below script to find the List of all Tables with Size and Row Count:



DECLARE @tbl table(Id int IDENTITY(1,1), Name varchar(256))


INSERT INTO @tbl
SELECT b.name + '.'+ a.name
FROM sys.tables a INNER JOIN sys.schemas b ON a.schema_id = b.schema_id


INSERT INTO @tbl
SELECT '-1'


DECLARE @result table(TableName varchar(256)
 , TotalRows int
 , Reserved varchar(50)
 , DataSize varchar(50)
 , IndexSize varchar(50)
 , UnusedSize varchar(50))


DECLARE @temp varchar(256)
DECLARE @index int
SET @index = 1
WHILE 1=1
BEGIN
SELECT @temp = NAME FROM @tbl WHERE Id = @index


IF @temp = '-1'
BREAK
INSERT @result( TableName, TotalRows, Reserved, DataSize, IndexSize, UnusedSize)
EXEC sp_spaceused @temp


SET @index = @index + 1
END


SELECT c.name+'.'+b.name as [table]
, a.*
 FROM @result a
INNER JOIN sys.tables b
ON a.TableName = b.name
INNER JOIN sys.schemas c
ON b.schema_id = c.schema_id
ORDER BY TotalRows DESC

Saturday, March 3, 2012

Backup MSSQL Database on Windows Dedicated Server

It is necessary to take Database regularly. You never know when Server face some problem, So it is necessary to take backup on your dedicated Windows Server. You can schedule the MSSQL Database to generate backup files from the below link :-
  1. Start Management Studio. Expand Databases. 
  2. Right Click on the data to be backup and Select “All Tasks” 
  3. Then Select “Backup Database”.
  4. SQL is configured to Select “Database - Complete” by Default.
  5. If you do not want to do this you can select “Database - Differential”.
  6. Generally, the Destination chosen by many users to save the SQL Backup file is “C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP”.
  7. Now, Double Click on the given default path and add “sqlbackup.bak” to the Existing Path.
  8. Then Click OK.
  9. Now, to Schedule backups select the “Schedule” option.
  10. Once done, Click “…” option.
  11. Now you will find a Recurring option i.e. Radio option. Click the option and Select “Change” option right after you do that.
  12. Select the Time on which you would like to have the backup with the Schedule of the Backup. You will have to enter the Time on which you would like to have the Backup Directory Created and also specificy if you wish to have the Backup Directory Created Daily, Weekly or Monthly.
  13. Now click “OK” on Each Dialogue box and proceed.
  14. You can also check if the Backup time and the Backups to be taken are Scheduled Successfully through “C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP” Directory.
  15. Now, In the Remote Data Backups only Select the SQL Backup Directory and you are done.