Connecting multiple servers in a single query window using SQLCMD in sql server

With the help of SQLCMD we can connect the multiple servers in a single sql query window. First we need to enable the SQLCMD.

Enabling SQLCMD Mode

For Enabling the SQLCMD Mode we need to Select Query à SQLCMD. See below.

  

Click ok. Now SQLCMD is enabled.

All SQLCMD commands start with a colon, and the command to change servers is ‘CONNECT’. To change to the local server using local host use the following command in a database query window 

:CONNECT BAGESH-PC

Basically it will connect the server and then disconnect that server. Now I am executing the script. Let’s see the result.

  

If we write any sql script it will connect to that server and get faired.

Let’s see I am connecting more than one server in a single window.

:CONNECT BAGESH-PC SELECT [Ename],[Eadd]   FROM [TestSSIS].[dbo].[Emp] :CONNECT BAGESH-PC\SQLSERVER2012 SELECT [Ename],[Eadd]   FROM [TestSSIS].[dbo].[Emp]

Both databases are on different servers.

Enable and disable all constraints on a table in SQL Server

SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Some time we need to delete the records from the child table we are getting error that

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Dept_ID". The conflict occurred in database "Emp", table "Dept", column 'DeptID'.
The statement has been terminated.

In this we have to delete the records in parent record. It will be bit difficult do this. For the best way to disable the constraints and delete the records from the child table and after that we need to enable the constraint.

 Disable Constraints

Disable the all constraints from a table

-- Disable all table constraints ALTER TABLE FactInternetSales NOCHECK CONSTRAINT ALL

  

Suppose if we want to disable the all constraint of the all table use below script

declare @temp table (              ID int IDENTITY(1,1),                 script varchar(500) ) insert into @temp SELECT      'ALTER TABLE [' + S.name + '].[' + T.name + '] NOCHECK CONSTRAINT ALL'                 AS [SQL to run:] FROM        sys.tables T JOIN        sys.schemas S ON          T.schema_id = S.schema_id ORDER BY    T.name select * from @temp

It will return the script to disable the all constraint.

  

Run the below script to disable the all constraint

declare @temp table (                 ID int IDENTITY(1,1),                 script varchar(500) ) insert into @temp SELECT 'ALTER TABLE [' + S.name + '].[' + T.name +  '] NOCHECK CONSTRAINT ALL'                 AS [SQL Script] FROM        sys.tables T JOIN        sys.schemas S ON          T.schema_id = S.schema_id ORDER BY    T.name Declare @MinID int=1, @MaxID int= (select Count(*) from @temp),@script varchar(500) while (@MinID<=@MaxID) BEGIN select @script=script from @temp where ID=@MinID exec (@script) SET @MinID=@MinID+1 END

Enable Constraint

If we want to enable the constraint for a table use below script

-- Enable all table constraints ALTER TABLE FactInternetSales CHECK CONSTRAINT ALL

Enable the constraint for all tables in a database

declare @temp table (                 ID int IDENTITY(1,1),                 script varchar(500) ) insert into @temp SELECT 'ALTER TABLE [' + S.name + '].[' + T.name + '] WITH CHECK CHECK CONSTRAINT ALL'                 AS [Sql script] FROM        sys.tables T JOIN        sys.schemas S ON          T.schema_id = S.schema_id ORDER BY    T.name Declare @MinID int=1, @MaxID int= (select Count(*) from @temp),@script varchar(500) while (@MinID<=@MaxID) BEGIN select @script=script from @temp where ID=@MinID exec (@script) SET @MinID=@MinID+1 END

Get Number of failure jobs in sql server

We can get the failure jobs using below sql script

select      JobHistory.server     ,job.name AS [Job Name]     ,JobStep.step_name As [Step Name]     ,JobHistory.sql_severity As [sql Severity]     ,JobHistory.message     ,(CASE JobHistory.run_status             WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded'             WHEN 2 THEN 'Retry'  WHEN 3 THEN 'Canceled'             WHEN 4 THEN 'In progress' END) AS Status     ,JobHistory.run_date as [Run Date]     ,JobHistory.run_time as [Run Time]        ,JobHistory.run_duration as [Run Duration] FROM msdb.dbo.sysjobs AS job INNER JOIN msdb.dbo.sysjobsteps AS JobStep  ON JobStep.job_id = job.job_id INNER JOIN msdb.dbo.sysjobhistory AS JobHistory ON JobHistory.job_id = job.job_id WHERE JobHistory.run_status = 0

See the below

 

Get the free size of server

With the help of below sql script we will get the free space of the server.

SELECT DISTINCT d.logical_volume_name AS LogicalName ,d.volume_mount_point AS DriveName ,CONVERT(NUMERIC(32,2),d.available_bytes *1.00/(1048576.0 *1024 )) AS FreeSpaceInGB ,CONVERT(NUMERIC(32,2),d.Total_Bytes *1.00/(1048576.0 *1024 )) AS TotalSizeInGB FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID) d ORDER BY FreeSpaceInGB

See the below

  

We can verify

  

In my system I stored some database on the G drive and some of the database on c drives. So it returns the free spaces on C and G drives. In Prod environment there are dedicated server for the database and configured log and temp databases on different drives. With the help of this sql we will get the free size of the drives.

Get the currently executing sql query

With the help of below sql script we will get the currently sql query executing on the database and we can get how long time the sql query have been taken.

SELECT      r.start_time [Start Time]               ,DateDiff(ss,r.start_time,GetDate()) as [execution time in seconds]             ,session_ID [SPID],             DB_NAME(database_id) [Database],             SUBSTRING(t.text,(r.statement_start_offset/2)+1,             CASE WHEN statement_end_offset=-1 OR statement_end_offset=0             THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1             ELSE (r.statement_end_offset-r.statement_start_offset)/2+1             END) [Executing SQL],             Status,command,wait_type,wait_time,wait_resource,             last_wait_type FROM        sys.dm_exec_requests r OUTER APPLY sys.dm_exec_sql_text(sql_handle) t WHERE       session_id != @@SPID -- don't show this query AND         session_id > 50 -- don't show system queries ORDER BY    r.start_time

  

Get the size of the database

With the help of below sql script we will get the size of the database on the sql server.

SELECT      sys.databases.name,             SUM(size)*8/1024 AS [Size of database in MB] FROM        sys.databases JOIN        sys.master_files ON          sys.databases.database_id=sys.master_files.database_id GROUP BY    sys.databases.name ORDER BY    sys.databases.name

  

We can also get the size of the database through wizard

Right click on the database and select the property

We will get the wizard

In general we will get the size of the database

Get the number of pages in a table on the database

The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

Sql server pages.

With the help of below sql script we will find the total number of pages in the server

--Get the list DataBase name DECLARE @tmpdatabase table (ID int IDENTITY (1,1), dbName varchar(100)) Insert into @tmpdatabase select name from  Master.sys.databases --select * from @tmpdatabase --Looping the Database DECLARE @FinalTable table ( RecordID int IDENTITY(1,1), ServerName varchar(100), DataBaseName varchar(100), tableName varchar(100), RowCounts int, TotalPages int, UsedPages int, UnusedPages int ) DECLARE @MinID int=1, @MaxID int = (select Count(*) from @tmpdatabase) while (@MinID<=5) BEGIN -- Total # of pages, used_pages, and data_pages for a given heap/clustered index declare @dName varchar(100) select @dName=dbName from @tmpdatabase where ID=@MinID DECLARE @s varchar(1000) set @s= 'SELECT     t.NAME AS TableName,     p.rows AS RowCounts,     SUM(a.total_pages) AS TotalPages,     SUM(a.used_pages) AS UsedPages,     (SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages FROM     '+@dName+'.sys.tables t INNER JOIN         '+@dName+'.sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN     '+@dName+'.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN     '+@dName+'.sys.allocation_units a ON p.partition_id = a.container_id WHERE     t.NAME NOT LIKE ''dt%''     AND t.is_ms_shipped = 0     AND i.OBJECT_ID > 255 GROUP BY     t.Name, p.Rows ORDER BY     t.Name' DECLARE @Table table ( RecordID int IDENTITY(1,1), tableName varchar(100), RowCounts int, TotalPages int, UsedPages int, UnusedPages int ) insert into @Table exec (@s) Declare @TMin int=1,@Tmax int =(select Count(*) from @Table) WHILE (@TMin<=@Tmax) BEGIN DECLARE @ServerName varchar(100) , @DataBaseName varchar(100), @tableName varchar(100), @RowCounts int, @TotalPages int, @UsedPages int, @UnusedPages int select @tableName=tablename,@RowCounts=RowCounts, @TotalPages=TotalPages, @UsedPages=UsedPages,@UnusedPages=UnusedPages from @Table where RecordID=@TMin insert into @FinalTable(ServerName,DataBaseName,tableName,RowCounts,TotalPages, UsedPages,UnusedPages)values                                          (@@SERVERNAME,@dName,@tableName,@RowCounts,@TotalPages, @UsedPages,@UnusedPages) Set @Tmin=@Tmin+1 End SET @MinID=@MinID+1 End select * from @FinalTable