SQL Server: Row Count Of All The Tables In Particular Database

Some times we want to find out the row counts for all the tables in a particular database in SQL Server. We can do it in different ways. I have tried to give two best ways to find out the row counts in below.

--Get Row Count Of All The Tables In SQL Server Database

SELECT object_name(p.object_id) TableName, sum(rows) [RowCount] FROM sys.partitions P
JOIN sys.objects O
ON P.object_id=o.object_id
and type='U'
Group by p.object_id
Go
---OR-----------
SELECT OBJECT_NAME(id) AS TableName,
       rowcnt          AS [RowCount] 
FROM   sysindexes si 
       INNER JOIN sys.tables tab 
 ON si.id = tab.OBJECT_ID 
 AND si.indid IN ( 0, 1, 255 ) 
WHERE  is_ms_shipped = 0 

Keywords: sql get row count,get row count, number of rows in a table sql,row count in sql, sql count rows in table,how to get count of rows in sql,sql get number of records

Read More

SELECT TOP COUNT dynamically

Scenario: Some times we want to make TOP Count as Dynamically then passing parameter to TOP command will not work. We have to add parameter with parenthesis[ ]

IF OBJECT_ID('[dbo].[sample]','U') IS NOT NULL
DROP TABLE dbo.sample

CREATE TABLE dbo.sample
(ID int,
name varchar(10));

INSERT INTO dbo.sample
SELECT 1,'a'
UNION
SELECT 2,'b'
UNION
SELECT 3,'c'
UNION
SELECT 4,'d'
UNION
SELECT 5,'e'
UNION
SELECT 6,'f'

--SELECT * FROM dbo.sample

DECLARE @noOfRows int
set @noOfRows = 3

SELECT TOP @noOfRows *  from dbo.sample -- Here it will through the rows 
SELECT TOP (@noOfRows) *  from dbo.sample -- This script will execute without any errors
Result:
 

Keywords: select top dynamically,dynamically select top sql,t sql select top dynamically,SELECT TOP COUNT dynamically

Read More

How to Identify a SQL Server Express Instance

Scenario:

Some times we want to know the current SQL Server Instance and for this use below simple query to get the result

SELECT @@SERVERNAME as Servername, @@VERSION as Version, SERVERPROPERTY('EDITION') as SQLedition

Result:

Read More

How to check if any view has metadata binding errors after DB refresh

Scenario: In my recent project after Source Database refresh we need to push to target server. We have views created in separate database by referring to target database and Views will be used in Cube. Here the problem we have seen is whenever we do some deployments and then DB refresh, we do face some metadata issues. So, we need to check each and every view to find out metadata issues by selecting view and its tedious task for us to check 100-150 views.

Solution: We have created one automation script which will give us the view name, error message and error linen umber

Code snippet: 

/** Script to find views which have metadata error*/ DECLARE @ViewName VARCHAR(255); DECLARE @ViewErrors TABLE   ( vw_name VARCHAR(255) , err_msg VARCHAR(MAX), err_line INT   ); DECLARE Error_Views CURSOR FOR   SELECT DISTINCT s.name+'.'+v.name  FROM sys.views V JOIN Sys.schemas  S ON V.schema_id=S.schema_id        OPEN Error_Views FETCH NEXT FROM Error_Views INTO @ViewName WHILE @@FETCH_STATUS = 0  BEGIN     BEGIN TRY    EXEC sp_refreshview @ViewName; IF @@error <> 0 BEGIN RAISERROR(15165,-1,-1,@ViewName)   END END TRY  BEGIN CATCH       INSERT  INTO @ViewErrors SELECT  @ViewName,ERROR_MESSAGE(), ERROR_LINE();  END CATCH      FETCH NEXT FROM Error_Views INTO @ViewName;   END    IF EXISTS ( SELECT 1 FROM   @ViewErrors ) -- Check if any erros exists   BEGIN  SELECT  *  FROM    @ViewErrors;   END CLOSE Error_Views DEALLOCATE Error_Views ---END-----

References: https://social.technet.microsoft.com/wiki/contents/articles/33565.how-to-check-if-any-view-has-metadata-binding-errors-after-db-refresh.aspx

Tags: Views, sql-view,sql-server,sp_refreshview all views,how to refresh data source view in ssas,sql server view,ssas data source view add column,

sp_refreshview all views,create view with schemabinding

Read More

Powershell Script for SSRS Deployment

Scenarios: Deploy SSRS reports using automation

#Set variables with configure values

$Environment = "DEV"

$reportPath ="/"

$reportFolder = "DemoReports"

$SourceDirectory = "E:\Test"  # local folder path where actual files exists

$DataSourcePath = "/"

$DataSet_Folder = "/"

$IsOverwriteDataSource =1

$IsOverwriteDataSet =1

$IsOverwriteReport =1

# Set server IP address based on Environment provided from Config file

IF( $Environment -eq "DEV")

 {

    $webServiceUrl = "http://server" # or http://xx.xxx.x..xxx

 }

 ELSEIF ( $Environment -eq "TEST")

 {

    $webServiceUrl = "http://server" # or http://xx.xxx.x..xxx

 }

 ELSEIF ($Environment -eq "PROD")

 {

    $webServiceUrl = "http://server" # or http://xx.xxx.x..xxx

 }

#Connecting to SSRS

Write-Host "Reportserver: $webServiceUrl" -ForegroundColor Magenta

Write-Host "Creating Proxy, connecting to : $webServiceUrl/ReportServer/ReportService2010.asmx?WSDL"

Write-Host ""

$ssrsProxy = New-WebServiceProxy -Uri $webServiceUrl'/ReportServer/ReportService2010.asmx?WSDL' -UseDefaultCredential

$reportFolder_Final = $reportPath +  $reportFolder

##########################################  

#Create Report Folder

        Write-host ""

        try

        {

            $ssrsProxy.CreateFolder($reportFolder, $reportPath, $null)

            Write-Host "Created new folder: $reportFolder_Final"

        }

        catch [System.Web.Services.Protocols.SoapException]

        {

            if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")

            {

                Write-Host "Folder: $reportFolder already exists."

            }

            else

            {

                $msg = "Error creating folder: $reportFolder. Msg: '{0}'" -f $_.Exception.Detail.InnerText

                Write-Error $msg

            }

          

        }

##########################################

#Create datasource

foreach($rdsfile in Get-ChildItem $SourceDirectory -Filter *.rds)

{

Write-host $rdsfile

#create data source

try

    {

        $rdsf = [System.IO.Path]::GetFileNameWithoutExtension($rdsfile);

       $RdsPath = $SourceDirectory+"\"+$rdsf+".rds"

        Write-host "Reading data from $RdsPath"

        [xml]$Rds = Get-Content -Path $RdsPath

        $ConnProps = $Rds.RptDataSource.ConnectionProperties

        $type = $ssrsProxy.GetType().Namespace

        $datatype = ($type + '.DataSourceDefinition')

        $datatype_Prop = ($type + '.Property')

        $DescProp = New-Object($datatype_Prop)

         $DescProp.Name = 'Description'

         $DescProp.Value = ''

         $HiddenProp = New-Object($datatype_Prop)

         $HiddenProp.Name = 'Hidden'

         $HiddenProp.Value = 'false'

         $Properties = @($DescProp, $HiddenProp)

        $Definition = New-Object ($datatype)

        $Definition.ConnectString = $ConnProps.ConnectString

        $Definition.Extension = $ConnProps.Extension

        if ([Convert]::ToBoolean($ConnProps.IntegratedSecurity)) {

            $Definition.CredentialRetrieval = 'Integrated'

        }

  

        $DataSource = New-Object -TypeName PSObject -Property @{

            Name = $Rds.RptDataSource.Name

            Path =  $Folder + '/' + $Rds.RptDataSource.Name

        }

            if ($IsOverwriteDataSource -eq 1)

            {

                [boolean]$IsOverwriteDataSource = 1

            }

            else

            {

                [boolean]$IsOverwriteDataSource = 0

            }

            $warnings = $ssrsProxy.CreateDataSource($rdsf, $reportFolder_Final ,$IsOverwriteDataSource, $Definition, $Properties)

          

          #  Write-Host $warnings

    }

    catch [System.IO.IOException]

    {

        $msg = "Error while reading rds file : '{0}', Message: '{1}'" -f $rdsfile, $_.Exception.Message

        Write-Error msgcler

    }

    catch [System.Web.Services.Protocols.SoapException]

 {

            if ($_.Exception.Detail.InnerText -match "[^rsItemAlreadyExists400]")

            {

                Write-Host "DataSource: $rdsf already exists."

            }

            else

            {

          

                $msg = "Error uploading report: $rdsf. Msg: '{0}'" -f $_.Exception.Detail.InnerText

                Write-Error $msg

            }

 

 }

 }

##########################################

 # Create Dataset

 Write-host "dataset changes start"

 foreach($rsdfile in Get-ChildItem $SourceDirectory -Filter *.rsd)

{

  Write-host ""

 $rsdf =  [System.IO.Path]::GetFileNameWithoutExtension($rsdfile)

       $RsdPath = $SourceDirectory+'\'+$rsdf+'.rsd'

  

    Write-Verbose "New-SSRSDataSet -RsdPath $RsdPath -Folder $DataSet_Folder"

  

 $RawDefinition = Get-Content -Encoding Byte -Path $RsdPath

 $warnings = $null

 $Results = $ssrsProxy.CreateCatalogItem("DataSet", $rsdf, $reportFolder_Final, $IsOverwriteDataSet, $RawDefinition, $null, [ref]$warnings)

  

 write-host "dataset created successfully"

  

}

#############################

#For each RDL file in Folder

foreach($rdlfile in Get-ChildItem $SourceDirectory -Filter *.rdl)

{

Write-host ""

#ReportName

 $reportName = [System.IO.Path]::GetFileNameWithoutExtension($rdlFile);

 write-host $reportName -ForegroundColor Green

 #Upload File

     try

    {

        #Get Report content in bytes

        Write-Host "Getting file content of : $rdlFile"

        $byteArray = gc $rdlFile.FullName -encoding byte

        $msg = "Total length: {0}" -f $byteArray.Length

        Write-Host $msg

        Write-Host "Uploading to: $reportFolder_Final"

        $type = $ssrsProxy.GetType().Namespace

        $datatype = ($type + '.Property')

        $DescProp = New-Object($datatype)

 $DescProp.Name = 'Description'

     $DescProp.Value = ''

     $HiddenProp = New-Object($datatype)

     $HiddenProp.Name = 'Hidden'

     $HiddenProp.Value = 'false'

     $Properties = @($DescProp, $HiddenProp)

        #Call Proxy to upload report

        $warnings = $null

 

       $Results = $ssrsProxy.CreateCatalogItem("Report", $reportName,$reportFolder_Final, $IsOverwriteReport,$byteArray,$Properties,[ref]$warnings)

       if($warnings.length -le 1)

        { Write-Host "Upload Success." -ForegroundColor Green

         }

        else

        { write-host $warnings

         }

    }

    catch [System.IO.IOException]

    {

        $msg = "Error while reading rdl file : '{0}', Message: '{1}'" -f $rdlFile, $_.Exception.Message

        Write-Error msg

    }

    catch [System.Web.Services.Protocols.SoapException]

 {

          

                $msg = "Error uploading report: $reportName. Msg: '{0}'" -f $_.Exception.Detail.InnerText

                Write-Error $msg

          

 

 }

##########################################

     ##Change Datasource

    $reportFullName = $reportFolder_Final+"/"+$reportName

    Write "datasource record $reportFullName"

    $rep = $ssrsProxy.GetItemDataSources($reportFullName)

    $rep | ForEach-Object {

    $proxyNamespace = $_.GetType().Namespace

    $constDatasource = New-Object ("$proxyNamespace.DataSource")

  

    $constDatasource.Item = New-Object ("$proxyNamespace.DataSourceReference")

    $FinalDatasourcePath =$reportPath+$reportFolder+"/"+ $($_.Name)

    $constDatasource.Item.Reference = $FinalDatasourcePath

    $_.item = $constDatasource.Item

    $ssrsProxy.SetItemDataSources($reportFullName, $_)

    Write-Host "Changing datasource `"$($_.Name)`" to $($_.Item.Reference)"

}

}

Write-host ""

Write-host "Successfully completed SSRS deployment" -ForegroundColor Magenta

Write-host ""

***
Script can be downloaded from beow MSDN link https://social.technet.microsoft.com/wiki/contents/articles/34521.powershell-script-for-ssrs-project-deployment.aspx

**********************
Keywords: 
ssrs automation , ssrs deployment automation, ssrs deploy powershell,ssrs deployment powershell
powershell ssrs deployment,powershell ssrs report,ssrs powershell cmdlets,ssrs powershell deploy,
ssrs powershell create folder, PowerShell Script Deploying SSRS Report

Read More