Get all the table size in a SQL Server Database with the help of T-SQL

With the help of the T-SQL query below we can measure the size of all the tables exists under a SQL Server Database

create table #Size (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
create table #TableSize (
    TableName varchar(255),
    NoOfRows int,
    ReservedSizeMB int,
    DatSizeMB int,
    ReservedIndexSizeMB int,
    UnusedSizeMB int)

EXEC sp_MSforeachtable @command1="insert into #Size
EXEC sp_spaceused '?'"
insert into #TableSize (TableName, NoOfRows, ReservedSizeMB, DatSizeMB, ReservedIndexSizeMB, UnusedSizeMB)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2)/1024,
SUBSTRING(data, 0, LEN(data)-2)/1024,
SUBSTRING(index_size, 0, LEN(index_size)-2)/1024,
SUBSTRING(unused, 0, LEN(unused)-2)/1024
from #Size

select *,TotalSpaceGB=(DatSizeMB+ReservedIndexSizeMB)/1024 from #TableSize
order by TableName

drop table #Size
drop table #TableSize

Stored Prodedure for SQL Server Agent Job Statistics and Status

There is no inbuilt view or SP for SQL Server Agent Job Statistics, for example in which state the job is, how much time it is taking. I created this Stored Procedure which will provide information related to the Jobs i.e. Job run date & time, job status, elapsed time, server name, error message (if job throws any error). Please execute the given SP below and follow the instruction to run it on your server.

1. Do not pass any filter if you want to see All Jobs (Succeeded, errored, running etc) 

    SQL Command to run -- Exec exec usp_Sql_Job_Status 

2. Pass 0 if you want to see only Error Job
 
    SQL Command to run -- Exec exec usp_Sql_Job_Status 0

3. Pass 1 if you want to see only Succeeded Job
 

    SQL Command to run -- Exec exec usp_Sql_Job_Status 1

4. Pass 2 if you want to see only Retry Job
 


    SQL Command to run -- Exec exec usp_Sql_Job_Status 2


5. Pass 3 if you want to see only Canceled Job
 
    SQL Command to run -- Exec exec usp_Sql_Job_Status 3


6. Pass 4 if you want to see only Running Job
 
    SQL Command to run -- Exec exec usp_Sql_Job_Status 4

CREATE PROCEDURE [dbo].[usp_Sql_Job_Status] 
    -- Add the parameters for the stored procedure here 
    @paramstatus int=Null 
AS 
BEGIN 
    -- SET NOCOUNT ON added to prevent extra result sets from 
    -- interfering with SELECT statements. 
    SET NOCOUNT ON; 
 
    -- Insert statements for procedure here 
    SELECT * FROM ( 
            SELECT  
                RunDate=DATEADD(SECOND, CASE WHEN LEN(run_time)=1 then RIGHT(CAST(run_time AS VARCHAR(6)),1) 
                                             WHEN LEN(run_time)=2 then RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                             WHEN LEN(run_time)=3 then LEFT(CAST(run_time AS VARCHAR(6)),1)*60+RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                             WHEN LEN(run_time)=4 then LEFT(CAST(run_time AS VARCHAR(6)),2)*60+RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                             WHEN LEN(run_time)=5 then LEFT(CAST(run_time AS VARCHAR(6)),1)*60*60+SUBSTRING(CAST(run_time AS VARCHAR(6)),2,2)*60 
                                                                                                                +RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                            WHEN LEN(run_time)=6 then LEFT(CAST(run_time AS VARCHAR(6)),2)*60*60+SUBSTRING(CAST(run_time AS VARCHAR(6)),3,2)*60 
                                                                                                                +RIGHT(CAST(run_time AS VARCHAR(6)),2) 
                                        END,  
                                CONVERT(DATETIME, CONVERT(CHAR(8), run_date))), 
                JobName=b.name, 
                StatusCode=run_status, 
                RunStatus=CASE WHEN run_status=0 THEN 'Error'  
                                WHEN run_status=1 THEN 'Succeeded' 
                                WHEN run_status=2 THEN 'Retry' 
                                WHEN run_status=3 THEN 'Canceled'  
                                WHEN run_status=4 THEN 'Running'  
                                ELSE 'Unknown' END, 
                ElapsedTime=run_duration, 
                StepName=step_name, 
                SqlMessageId=sql_message_id, 
                SqlSeverity=sql_severity, 
                [Message]=[message], 
                [Server]=[server] 
            FROM  
                msdb.[dbo].[sysjobhistory] a  
                JOIN  
                    msdb.[dbo].[sysjobs] b  
                ON a.job_id=b.job_id 
        UNION 
            SELECT  
                RunDate=jact.run_requested_date, 
                JobName=jv.name, 
                StatusCode=4, 
                RunStatus='Running', 
                ElapsedTime=DATEDIFF( SECOND, jact.run_requested_date, GETDATE() ) , 
                Null, 
                Null, 
                Null, 
                Null, 
                [Server]=jv.originating_server 
            FROM  
                msdb.dbo.sysjobs_view jv 
                JOIN 
                    msdb.dbo.sysjobactivity jact 
                ON  
                    jv.job_id = jact.job_id 
                JOIN 
                    msdb.dbo.syssessions ss 
                    ON 
                    ss.session_id = jact.session_id 
                JOIN 
                    ( 
                        SELECT 
                            MAX( agent_start_date ) AS max_agent_start_date 
                        FROM 
                            msdb.dbo.syssessions 
                    ) sess 
                    ON 
                        ss.agent_start_date = sess.max_agent_start_date 
                WHERE  
                    run_requested_date IS NOT NULL AND stop_execution_date IS NULL 
         
 )QUERY 
 WHERE (StatusCode=@paramstatus) OR (-1=isnull(@paramstatus,-1)) 
 ORDER BY RunDate DESC  
END 

cheers,

SSAS hands on Part 1

We will start with AdventureWorksDW2012 database by which we will explore most of the SSAS features. We will use subset of the database with below set of tables

DimProduct

DimProductSubCategory

DimProductCategory

DimDate

DimReseller

DimEmployee

DimPromotion

DimCurrency

DimSalesTerritory

DimGeography

FactResellerSales

Below is the diagram of data mart.

 

Now to create a new SSAS project follow below step

First create a new SSAS project from FileàNewàProject Menu

 

Select Analysis Service MultiDimensional and Data Mining Project and give a name to the project.

 

      Create a Data Source by right clicking and selecting new Data Source on right hand side Solution Explorer Window

 

Create a Data Source View (by selecting above tables) by right clicking and selecting new Data Source View on right hand side Solution Explorer Window

 

You will get the dsv created in your SSAS project like below

 

In next part we will start and learn Dimension Design in SSAS.

How to export data from SQL Server table to excel file without using SSIS

In this post we will see how we can export SQL Server table data into excel file. Here i am using xlsx if you want to work with xls then you have to change the driver in openrowset function.

I will be using dbo.ProductCategory table from Adventureworks database. First we need to create a template excel file with Column headers.

It will look like below

Now we can use below step and either create one stored procedure or create one SQL Server agent Job.

--Step 1

exec xp_cmdshell 'copy d:\temp\Product.xlsx d:\work\Product.xlsx'

--Step 2

insert into OPENROWSET(

'Microsoft.ACE.OLEDB.15.0', 

'Excel 12.0;Database=d:\work\Product.xlsx;;HDR=YES', 

'SELECT * FROM [Sheet1$]')

select * from [ProductCategory]

IF you need to work on excel (xls) then use code below

insert into OPENROWSET(

'Microsoft.Jet.OLEDB.4.0', 

        'Excel 8.0;Database=d:\work\Product.xls;;HDR=YES', 

        'SELECT * FROM [Sheet1$]')

select * from [ProductCategory]

After running this command the data will be populated in the excel as below

SSAS hands on Part 3 (Dimension Design Contd.)

In this session we will create Employee Dimension and will learn how to create parent child hierarchy and ssas features.

We can create Employee dimension in similar way as we created in last post, select DimEmployee in dimension wizard. Now for name colum we don’t have any suitable attribute to select so in DSV we will create one column “EmployeeName” which will be a combination of First, middle and last name.

Right click on DimEmployee and click on New Named Calculation.

Write the expression as below and click ok. It will create a new column EmployeeName

Now go to Dimension design wizard and select DimEmployee. Change the Name Column property with the attribute EmployeeName we just created in step 2.

If you want to create employee hierarchy with sales territory then check the related table DimSalesTerritory otherwise uncheck it and click next. I am including DimSalesTerritory here.

Now select the attribute you want and rename them as per your requirement

I have changed here Employee key, Parent Employee key, sales territory key to Employee, ParentEmployee, SalesTerritoryRegion.

Give name to dimension and click finish button

Change the Name column property of ParentEmployee from Employee Key to EmployeeName

As ParentEmployee attribute is the parent in nature so change the usage property from key to Parent.

Now we see ParentEmployee hierarchy which have different levels Level01, Level02 and soon, as below

what if we will give these levels some useful names i.e. CEO, Sr Manager, Manager, Lead, Associates.  Go to the ParentEmployee attribute property and click NamingTemplate property and give them meaningful names

Click ok and process dimension and see result below

We still see that All level, what if we want to start with CEO level. Select ParentEmployee and change the value of IsAggregatable property to false as below

After dimension redeploying the All level will be removed as below

Stay tuned...In next Post we will explore some more SSAS features. 

Loading multiple files into tables with TSQL

With the help of this code we can load multiple files into SQL Server table without using SSIS.

I tested this code by creating two excel files i.e. ProductCategory.xlsx & ProductCategory1.xlsx at path D:\Project\Files\ and loaded into the table dbo.ProductCategory.


IF OBJECT_ID('tempdb..#tempOpex') IS NOT NULL DROP TABLE #tempOpex
Declare @paramFilePath varchar(255)='D:\Project\Files\',@Sql varchar(max),@month int,@budVer varchar(3),@id int,
@fileToSelect varchar(3),@fileExists INT,@FileContents  VARCHAR(100),@paramFileName varchar(255),@paramfullpath varchar(500);

DECLARE @Opex TABLE
(
 Name varchar(255),
 Depth INT,IsFile INT
)

INSERT @Opex (Name,depth,IsFile)
EXEC master.sys.xp_dirtree @paramFilePath,1,1;


select Id=row_number() over(order by Name),* into #tempOpex from  @Opex

Select @id=max(Id) from #tempOpex

While (@id>=1)
Begin

Select @paramFileName=Name from #tempOpex where Id=@id
Set @paramfullpath=@paramFilePath+@paramFileName

EXEC master.dbo.xp_fileexist @paramfullpath, @fileExists OUTPUT

IF (@fileExists=1)
Begin

SET @Sql = 'Select * from OPENROWSET(''Microsoft.ACE.OLEDB.15.0'',
''Excel 12.0; HDR=YES;IMEX=1;Database='+@paramfullpath+''',''Select * from [ProductCategory$]'')'
INSERT INTO dbo.ProductCategory
EXEC (@Sql)
Set @id=@id-1
End
Else
Begin

Print 'File Does not exists'
End
End

If code gives following error

"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online."

then run the code below

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

SSAS hands on Part 2 (Dimension Design)

In this post we will learn how to design dimensions and will look deeper the options available in SSAS for dimension designing.

Let's start with Product Dimension

Right click on Dimension and select New Dimension on Solution Explorer Window

 

Click next on Dimension Wizard window pop up

Now select option “Use and existing table” and click next

 

Select dimension DimProduct and click next

 

Change the Name column value here I changed from Product Key to “English Product Name”, this we can change from property window later also. We changed the Name Column property from Product key to English Product Name because so that when we will drag product key then it will display product name instead of key which the users want. Click Next

 

1.      Select the related tables (ProductCategory & ProductSubCategory) on next page. If you don’t need attribute from these tables then you can uncheck them but we will create hierarchy so check them and click next

 

Select the attribute which you want in your dimension and click next

Give a name to your dimension and press finish

 

I have renamed some of the attribute names (Product Subcategory Key, Product Category Key, English Description, Product Key to ProductSubcategory, ProductCategory, Description, Product

 

Set the Name Column Property for ProductCategory & ProductSubcategory attribute to EnglishProductCategoryName & EnglishProductSubcategoryName otherwise when user browse the data it will show him keys, so click on the KeyColumn Property in Property window and select EnglishProductCategoryName and click ok.

 

 

Similarly do for Subcategory.

Now we will create Hierarchy�� Drag all attribute which will be in hierarchy in middle pane. Also change your hierarchy name from Hierarch to Product By Category

 

Define attribute relationship properly from child to master.

 

Now deploy solution from solution explorer window

 

Now click on Browser tab to check the data

 

Here we see that we can see created hierarchy “Product By Category” and the attribute Product, ProductCategory & ProductSubcategory individually so we can hide them from browsing. To do this go to Dimension structure tab. Go to the property window of attribute ProductCategory and change the property  “AttributeHierarchyVisible” from True to False. Repeat this for all the attribute which you do not want to repeat. Now Deploy, reconnect and browse again

 

When we browse data we see one value “unknown” as below

 

we can hide it. Click on DimProduct under dimension structure tab and change the value of UnknownMember property from Visible to Hidden

 

Now if we browse it then unknown will be removed

 

In part 3 we will go through next dimensions and some more concepts in details.