Fetch row count of specific table against all database in SQL Server

Below query will list out the count of rows against your SQL server all databases.
And If you want to exclude any Database then please mention them in @DBName variable.

Declare @MaxRow int, @MinRow int =1, @Sql nvarchar(Max), @DbName as nvarchar(Max)

Create table #B(RowNum  Bigint, DBName nvarchar(Max))

SELECT name, row_number() over(order by name ) as rowid into #A FROM master.dbo.sysdatabases

SET @MaxRow = (SELECT MAx(rowid) from #A);

WHILE (@MaxRow>@MinRow)

BEGIN

    SET @DbName= (Select name from #A where rowid = @MinRow);

    If (@DbName not in ('master', 'tempdb', 'model', 'msdb'))

    BEGIN

          SET @Sql = 'Use ['+@DbName +']

                           --GO

                       Select Count(1) as Cnt, '''+@DbName+''' as DBName from ['+@DbName+'].[dbo].[tblData] with(nolock)

                       --GO

                       '

          --print @Sql;

        Insert into #B  EXEC (@Sql) ;

    END

    SET @MinRow = @MinRow+1 ;

END

 Select * from #B order by RowNum DESC

 Drop table #A

 Drop table #B

 This query can be modified more but for and we avoid the use of temp table but for now its ok. If i or someone find any issue with SQL performance then i will update this blog with more optimized query,

Find what is going on with your SQL Server

Hello Guys, 

This blog is to tell you the way how you can find the things which are running in your SQL Server. Those things can be locks on your SQL Server, batch jobs, SQL queries, procedure etc. 

The way which i already know was executing procedure of either "SP_WHO" or new optimized with blockby column"SP_WHO2". 

Today i have learnt new way to do that from blog of SQL consultant who has optimized this process more in really broad way. Things which i have learnt from his blog , i am going to right them down below and i hope that will help you guys too.

-- Get the details whats going on in your SQL server

EXEC SP_WHO

-- It will give you few extra column from sp_who proc Which will help you to find which SPID is block by which SPID by having a quick watch in blockBy column

EXEC SP_WHO2

-- Here 55 is SPID which i took from sp_who or sp_who2 proc which is in blkby column

DBCC INPUTBUFFER(55)

 -- "SP_WHOISACTIVE" that you need to download from a blog by Adam (http://sqlblog.com/files/folders/beta/entry42453.aspx) and run the SQL script in your SQL server it will give you the statistics of your SQL Query

-- it will return the actual Query which are doing something with your SQL Server

-- you can also see the locks, waiting time, read and write time etc.

-- First you should start by executing "Sp_WHOISACTIVE" without any parameter. After when you feel comfortable with result then you should go a head with parameters.

-- I have learnt about two parameters till now which will give you the result with client statistics.

-- ** Note : if your server is really busy then try to optimize query first to get specific SPID which is causing issue according to you because in busy server there would be a lot of things going on and if you go with all then it will take long time to determine the things.

EXEC SP_WHOISACTIVE

         @get_plans=1,

           @get_transaction_info=1

View XML nodes in SQL Server as separate column

Hi, today i was asked by someone to view Xml file nodes in SQL Server as separated columns to check the consistency of data available in nodes.

I already knew that because i have done those kinds of stuff in beginning of my developer carrier.

Below Query will give you exact out but its little bit judicious because you have to give nodes name manually. I am working out to get the simple and easy solution when i will achieve my goal i will update this blog.

Demo xml data='<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>

<Root source="C:\Data.csv" datetime="2015-05-12T16:16:55+06:00" >

     <record num="1">

          <ID>131383</ID>

          <EmpNum>51023</EmpNum>

          <Login>Rty</Login>

          <First_name>Amit</First_name>        

     </record>

    '

SELECT @XmlFile = BulkColumn

FROM  OPENROWSET(BULK 'path of xml', SINGLE_BLOB) x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XmlFile

SELECT ID, EmpNum, [Login],First_name

FROM OPENXML(@hDoc, 'Root/record')

WITH

(

ID varchar(50) '(ID)[1]',

EmpNum varchar(50) '(EmpNum)[1]',

[Login] varchar(100) '(Login)[1]',

First_name varchar(100) '(First_name)[1]'

)

EXEC sp_xml_removedocument @hDoc

GO

Search record among multiple columns of SQL table.

Today i have learnt a great thing related to searching a value among all columns of a SQL table. That is very useful even from this i can search a value in specific column of all table in SQL database.

Suppose you have a table this :

CREATE TABLE tblTest
(id INT IDENTITY(1,1),
columnA NVARCHAR(50),
coulmnB INT,
coulmnC NVARCHAR(250))-- Create a table 

INSERT INTO tblTest (columnA,coulmnB,coulmnC) VALUES
('Grim',2,'Master'),
('Kingdom',200,'Amar'),
('Paula',300,'Sara'),
('Kingdom',400,'Grim')--Inserting some Dummy Records

Now you have a requirement to search value 'Grim'  in a table for all column. In that case you will think to put where clause on each column of SQL table. but there is an alternative to do that. You can user below query to search a value in all columns of SQL table.

SELECT * FROM tblTest WHERE 'Grim' IN (columnA,coulmnB,coulmnC)

How to filter duplicate rows (duplicate records by column)

Today my old project manager ask me for a favor to give him a SQL query which exclude duplicate rows.

let me direct come to the point and show you some pictorial presentation.

--Query--
select data.provider_id, data.person_id, data.rank, data.RowNumber from
 (
 select provider_id, person_id
 , Rank()
 over (
 PARTITION BY provider_id
 ORDER BY provider_id ) AS [rank],
 ROW_NUMBER() OVER (
 PARTITION BY provider_id
 ORDER BY provider_id DESC ) as RowNumber
 from testtble

 ) as data
 where data.RowNumber <= 5
 order by provider_id

you can see that in row 2 and 3 provider and person id's are repeated.
To avoid the duplicacy i have to first find the duplicate rows based on columns records.
So i have created below query to remove that duplicacy from result.

--Query--
;WITH CTE AS(
SELECT *,(RowsNumber - Rank_Number + 1) AS Rank_Item FROM
    ( SELECT PROVIDER_ID ,
                PERSON_ID ,
                ROW_NUMBER() OVER( ORDER BY provider_id) AS RowsNumber ,
                RANK() OVER( ORDER BY provider_id) AS Rank_Number
           FROM(
        SELECT
  PROVIDER_ID ,
  PERSON_ID ,
  COUNT(*) AS CNT
                   FROM
  TESTTBLE
                   GROUP BY
  PROVIDER_ID ,
                            PERSON_ID
               )A
       )B
     )SELECT * FROM CTE WHERE Rank_Item<=5
      ORDER BY PROVIDER_ID;

   

Find an item from database according to char index

Hi ,

I have found the another way to find an item from SQL Server according to the position of character.
There are many ways to do it.

One way to do is use 'like' with '%' operator
Second way to do is  use 'like' with '_' operator
Third way to do is use charIndex function of SQL Server

Suppose you have find a record from database whose length should not greater then 12 and first char should be 'T' and last char should 'Z'.

In this case you cannot use like with %  because result will not depends upon length of parameter provided.

I would suggest to try this with your table and see the results you get. It will also give you a little learning.


SELECT  * FROM 
                tblData
WHERE 
                DisplayText like 'T%Z'



SELECT  * FROM 
                tblData
WHERE 
                DisplayText like 'T__________Z'

Get Updated Row Data

I never knew that such kind of things are there in SQL Server. Its amazing every person who works on SQL should know that. Its awesome.

 

Try those below queries with your table and columns 

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [ID]

      ,[tblname]

      ,[UpdateTime]

  FROM [SQLToLinq].[dbo].[tblMNames]

  

  UPDATE 

                 tblMNames

  SET 

                 tblname='tblMnames'

  OUTPUT 

                INSERTED.UpdateTime , INSERTED.tblname

  WHERE 

                  tblname='tblMName'