Get All the TABLES with Number of Rows and Columns

SELECT OBJECT_NAME(D.object_id) As [TABLE NAME], 
  SUM(row_count) [ROWS COUNT] , 
  Count(C.Column_Id) [COLUMN COUNT]
FROM sys.dm_db_partition_stats As D Inner JOIN sys.objects As o
  On o.object_id = D.object_id And o.type = 'u'
  Inner Join sys.columns As C On C.object_id = D.object_id
WHERE [index_id] IN (0,1)
GROUP BY D.object_id

Find Current Location of Data and Log File of All the Database and get size of databases

Some time for carring data from one machine to another machine we need to copy database file

for that we need to copy database ldf and mdf file becaues this is eassiest way yo carring database. for this  we must be know the these db files are where is located on server so we can copy this.

First way to write this query on our Query window

SELECT name, physical_name AS current_file_location
FROM sys.master_files

Second way to find the location with Appropriate database file size 

SELECT DB_NAME(mf.database_id) AS databaseName
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
ORDER BY 3 DESC 

Third way to find database details with size in shortest form that you wanted to find

SELECT
DB_NAME(mf.database_id) AS databaseName,
name as File_LogicalName,
case
when type_desc = 'LOG' then 'Log File'
when type_desc = 'ROWS' then 'Data File'
Else type_desc
end as File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 as size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 as size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 as size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
ORDER BY num_of_Reads DESC
 

 

Xml data type is not supported in distributed queries. Remote object

I found one issue while coping records from latest version of sql to old version of 

sql where xml datatype is not supported using linked server while fetching records 

i found following error occurred
"Xml data type is not supported in distributed queries. Remote object 

   'Server1.dbMurli.dbo.TB_Murli' has xml column(s)." 

Root Cause :
- XML column can not be accessed directly from the remote server....
- Following is the table structure in Remote server and Local server

CREATE TABLE TB_Murli
(
ID  INT,
Column1  VARCHAR(10),
[Address] XML
) 

Solution :
So, we can solve this issue as given below...

INSERT TB_Murli
SELECT * FROM 
(
SELECT * FROM OPENQUERY(Server1,'SELECT ID, Column1,CAST([Address] AS NVARCHAR(MAX)) 

  [Addres] FROM dbMurli.dbo.TB_Murli')
)AS XUsign the "OPENQUERY" 

we can solve the issue...  

In Short :-  

XML is not supported in distributed queries. You could write a passthrough query with OPENQUERY, and cast the XML column to nvarchar(MAX). For instance:
SELECT cast(xmlcol as xml) FROM OPENQUERY(REMOTESVR, 'SELECT cast(xmlcol AS nvarchar(MAX)) FROM db.dbo.tbl')

Insert Records from one table to another table

Before records insertion from one table to another table we must need to aware of these steps

1. New table need to be create with data.
2. Table is exists but we need to copy all records.
3. Table is exists and we wanted to copy particular records on existing table.

How to copy records for all the above steps.

Note : - Consider we have tblSource with all records Ex:-
Create table tblSource(Id int Primary Key,Name varchar(50), Address varchar(100),Created datetime default getdate())
Insert into tblSource(Id,Name,Address) values (1,'Murli','Mumbai-Sanpada')
Insert into tblSource(Id,Name,Address) values (2,'Deepak','Mumbai-Vasai')
Insert into tblSource(Id,Name,Address) values (3,'Ajit','Mumbai-Thane')


1. New table need to be create with data when target table is not exists.
       Select * Into tblIntoSource from tblSource


2. Target table exists and i wanted to copy all the records from source table.
       Insert tblSourceCopy Select * from tblSource


3. Target table exists and i wanted to copy particular records on it... i have two option to do this.
  A. Using Into Command
       Insert into tblDest(Id,Name) Select Id,Name from tblSource

  B. Using Direct Insert Command
       Insert tblSourceCopy1(Id,Name) Select Id,Name from tblSource