script for insert data sql statement

Some times we need insert into select statement for tables from one database to other database. Here is the script which will create the script for this process.

This can be useful who are using SQL Server 2000 or SQL Server 2005.
For those who are using SQL Server 2008 please look at here

Insert Script with Data SQL server 2008

Tables having idenity key

select ‘set identity_insert ‘+ name + ‘ on ‘ + char(9)+ char(13) + ‘insert into [‘ + name +’](‘+
stuff((select ‘,’+name as [text()] from sys.columns where object_id =t.object_id
for xml path(”)) ,1,1,” )
+’) select ‘ + stuff((select ‘,’+name as [text()] from sys.columns where object_id =t.object_id
for xml path(”)) ,1,1,” ) +’ from [linked server].[linked db].[dbo].[‘ + name +’] with (nolock)’ +
char(9)+char(13) + ‘set identity_insert ‘ + name + ‘ off ‘
from
sys.tables t
where object_id in (
select object_id from sys.identity_columns)

tables not having identity_key
select ‘insert into ‘+ name +
‘ select * from [linked server].[linked db].[dbo].[‘ + name +’] with (nolock)’
from
sys.tables t
where object_id not in (
select object_id from sys.identity_columns)

How to get size info for database file using dbcc showfilestats

CREATE TABLE freefilespace

    (

         fileid       INT,

         filegroup    INT,

         totalextents INT,

         usedextents  INT,

         dbname       VARCHAR(256),

         filename     VARCHAR(2000)

    )

INSERT INTO freefilespace

EXEC (‘DBCC showfilestats’)

SELECT dbname,

       filename,

       fileid,

       filegroup,

       (totalextents * 64 )                        filesize_kb,

       usedextents *64                             usedsize_kb,

       (totalextents * 64 )– ( usedextents * 64 ) freespace_kb

FROM   freefilespace

Undocumented Commands

Note:-All the samples here are run against AdventureWorks sample DB

DBCC proccache

Provides procuedure cache information

DBCC proccache

dbcc showfilestats

It shows total size and used size in extents.
If you multiply it by 64 you will get size in KB.

dbcc showfilestats

DBCC cachestats

show cache information cache hit ratio , number of times cache is used and pages used.

DBCC cachestats

DBCC activecursors
DBCC activecursors [SPID]

Provide activecursors information for selected DBCC Commands

DBCC activecursors (59)

DBCC showtableaffinity

DBCC showtableaffinity (‘person.address’)

DBCC detachdb

It will detach database.

DBCC detachdb (‘db name’)

sp_msforeachtable
sp_msforeachtable is will execute the command against every table in the database.

Exec sp_msforeachtable ‘select convert(varchar(512),”?”),count(*) from ?’

sp_msindexspace

sp_MSindexspace tablename , index_name

Will provide information of index_id, index_name, size, additional comment for all index for selected table.

exec sp_msindexspace ‘[sales].[storecontact]’

sp_MStablespace

sp_MStablespace tablename

Provide information for tables space

sp_mstablespace ‘person.address’

sp_msforeachdb
Will run the command for each database in the server

exec sp_msforeachdb ‘sp_spaceused’

sp_MShelpindex

sp_MShelpindex tablename , indexname , flags

sp_mshelpindex ‘person.address’

Returns index name, status , index id, fillfactor , columns, fulltext key, order etc…

sp_MShelpcolumns

Provide information for columns of a table
sp_MShelpcolumns ‘person.address’

sp_MShelptype
Provides information about system data types and user data types.

exec sp_MShelptype

xp_fixeddrives
Provide free space information for each disk.

exec xp_fixeddrives

lets play with XML

Here is Nested XML which contains data from 2 tables ,Customer and Order
I want to get data for this 2 tables from XML

declare @xmlData XML
set @XMLData =
‘<root><row>
<custid>1</custid>
<custname>amish</custname>
<order>
<orderdetail orderid = “1” orderdate = “2008-01-01” />
<orderdetail orderid = “2” orderdate = “2008-01-02” />
</order>
</row>
<row>
<custid>2</custid>
<custname>nikita</custname>
<order>
<orderdetail orderid = “3” orderdate = “2008-01-03” />
<orderdetail orderid = “4” orderdate = “2008-01-04” />
</order>
</row>
</root>’

DECLARE @handle1 INT
DECLARE @handle2 INT

EXEC sp_xml_preparedocument @handle1 OUTPUT, @xmlData;
EXEC sp_xml_preparedocument @handle2 OUTPUT, @xmlData

SELECT * FROM OPENXML (@handle1, ‘/root/row’, 2) WITH ( custid int, custname varchar(450))

SELECT * FROM OPENXML (@handle2, ‘/root/row/order/orderdetail’)
WITH ( orderid int ‘@orderid’,
orderdate varchar(20) ‘@orderdate’,
custid int ‘../../custid’)

EXEC sp_xml_removedocument @handle1
EXEC sp_xml_removedocument @handle2