Sql Server & SSRS Tips

Get All Parent and Child Companies (plus child of child companies) up to Nth level using recursive CTE declare @companyId int set @companyId = 1 ;WITH cte AS (     SELECT CompanyId, CompanyName, ParentCompanyId, 0 as steps     FROM dbo.Company     --WHERE ParentCompanyId IS NOT NULL      WHERE companyid = @companyId   UNION ALL     SELECT  c.CompanyId, c.CompanyName, c.ParentCompanyId, cte.steps +1 as steps     FROM dbo.Company AS c     inner JOIN cte ON cte.CompanyId = c.ParentCompanyId ) SELECT CompanyId, CompanyName, ParentCompanyId, steps FROM cte;

Source: https://blogs.msdn.microsoft.com/buckwoody/2007/09/24/script-of-the-day-count-all-objects-in-a-database/ SELECT  'Count' = COUNT(*) ,         'Type' = CASE type                    WHEN 'C' THEN 'CHECK constraints'                    WHEN 'D' THEN 'Default or DEFAULT constraints'                    WHEN 'F' THEN 'FOREIGN KEY constraints'                    WHEN 'FN' THEN 'Scalar functions'                    WHEN 'IF' THEN 'Inlined table-functions'                    WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints'                    WHEN 'L' THEN 'Logs'           ...

--Create a target table CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY ) GO --Insert records into target table INSERT INTO Products VALUES (1, 'Tea', 10.00), (2, 'Coffee', 20.00), (3, 'Muffin', 30.00), (4, 'Biscuit', 40.00) GO --Create source table CREATE TABLE UpdatedProducts ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY ) GO --Insert records into source table INSERT INTO UpdatedProducts VALUES (1, 'Tea', 10.00), (2, 'Coffee', 25.00), (3, 'Muffin', 35.00), (5, 'Pizza', 60.00) GO SELECT * FROM Products SELECT * FROM UpdatedProducts GO ----------------------------------------------------- --Synchronize the target table with --refreshed data from source table MERGE Products AS TARGET USING UpdatedProducts AS SOURCE ON ( TARGET.ProductID = SOURCE.ProductID ) --When records are matched, update --the records if there is any...

Change Data Capture (CDC) in SQL some cdc helpful links: https://www.red-gate.com/simple-talk/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/ https://sqlblog.org/2007/06/21/playing-with-cdc-in-katmai

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/2/2017 -- Description: To Get All the Names of tables and Databases whose data have changed in last 24 Hours -- ============================================= ALTER PROC UP_FindAllDatabaseHaveChanges AS     BEGIN         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             (               ID INT IDENTITY(1, 1)                      PRIMARY KEY ,       ...

USE [master] GO /****** Object:  Database [TrackingDB]    Script Date: 1/5/2017 9:24:50 AM ******/ CREATE DATABASE [TrackingDB]  CONTAINMENT = NONE  ON  PRIMARY ( NAME = N'TrackingDB', FILENAME = N'D:\TrackingDB_DO NOT DELETE\TrackingDB.mdf' , SIZE = 380928KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  LOG ON ( NAME = N'TrackingDB_log', FILENAME = N'D:\TrackingDB_DO NOT DELETE\TrackingDB_log.ldf' , SIZE = 517184KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) GO ALTER DATABASE [TrackingDB] SET COMPATIBILITY_LEVEL = 110 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [TrackingDB].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [TrackingDB] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [TrackingDB] SET ANSI_NULLS OFF GO ALTER DATABASE [TrackingDB] SET ANSI_PADDING OFF GO ALTER DATABASE [TrackingDB] SET ANSI_WARNINGS OFF GO ALTER DATABASE [TrackingDB] SET ARITHABORT OFF GO AL...

-- Create a login for SQL Server by specifying a server name and a Windows domain account name. USE [master] CREATE LOGIN [XDEV\mjawad] FROM WINDOWS; GO -- OR use the code below to Create a SQL Server User --USE [master] --GO --CREATE LOGIN applogin WITH PASSWORD=N'applogin', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON --GO -- Cursor For assigning roles to the specified Users on all databases ------***************************************************************** For DBA'S USE master GO DECLARE @dbname VARCHAR(50) DECLARE @statement NVARCHAR(max) DECLARE @UserName NVARCHAR(100) SET @UserName='[XDEV\DBA]'     ----***** Set User Name here to give permission ******** DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE --name NOT IN ('master','model','msdb','tempdb') AND state=0 OPEN db_cursor FETCH NEXT FROM db_cursor INTO @dbname WHILE @@FETCH_STATUS =...