Employee Manager Query

Classic Employee Manager Problem
This is a classic interview question.

CREATE TABLE emp

(
empid int not null primary Key,
mgrid int,
empname char(10) 
)
go
alter table emp
add constraint fk_empxmgr Foreign key (mgrid) references emp(empid)
 
go
INSERT emp SELECT 3,NULL,'Sonu'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 1,2,'Viru'
INSERT emp SELECT 4,2,'Radhey'
INSERT emp SELECT 5,2,'Jai'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
 
;WITH EmpsCTE (empid, mgrid, level)
AS
(
SELECT empid, mgrid, 0
FROM emp e
WHERE mgrid IS NULL
UNION ALL
SELECT e.empid, e.mgrid, m.level + 1
FROM emp e
INNER JOIN EmpsCTE AS m
ON e.mgrId = m.empid
)
SELECT e.empname employee,m.empname Manager, ct.level Level 
FROM EmpsCTE ct
inner JOIN emp e on e.empid = ct.empid
left JOIN emp m on m.empid = ct.mgrid


regards
DB

Find Object Name Across All Databases On Server

FIND OBJECT NAME ACROSS ALL DATABASES ON SERVER

CREATE TABLE #dbTmp (id int identity(1,1), dbName nvarchar(200))

CREATE TABLE #dbName (id int identity(1,1), dbName nvarchar(200), tblName nvarchar(200), cols int, maxRowSize int)

DECLARE @cnt int = 0

DECLARE @dbTmp varchar(200)

DECLARE @tblTmp varchar(200)

DECLARE @var varchar(1000)

INSERT INTO #dbTmp

SELECT name as dbName from sys.databases where name NOT in ('model', 'master', 'msdb', 'tempdb', 'ReportServer','ReportServerTempDB')

SELECT @cnt = @@Rowcount

WHILE @cnt <> 0

BEGIN

SELECT @dbTmp = dbName from #dbTmp where id = @cnt

SELECT @var = 'INSERT INTO #dbName (dbName, tblName) '

SELECT @var = @var + 'SELECT ''' + @dbTmp +''', OBJECT_NAME (sc.[object_id]) tablename FROM ['

+@dbTmp+'].sys.all_objects sc where Name like ''%CNTRY02%'''

PRINT @var

EXECUTE (@var)

SELECT @cnt = @cnt - 1

END

SELECT * FROM #dbName

DROP TABLE #dbTmp

DROP TABLE #dbName

 

--DB

Get Row Size of all the tables of All the DBs on a server

Get Row Size of all the tables of All the DBs on a server

CREATE TABLE #dbTmp (id int identity(1,1), dbName nvarchar(200))

CREATE TABLE #dbName (id int identity(1,1), dbName nvarchar(200), tblName nvarchar(200), cols int, maxRowSize int)

DECLARE @cnt int = 0

DECLARE @dbTmp varchar(200)

DECLARE @tblTmp varchar(200)

DECLARE @var varchar(1000)

INSERT INTO #dbTmp

SELECT name as dbName from sys.databases where name NOT in ('model', 'master', 'msdb', 'tempdb', 'ReportServer','ReportServerTempDB')

SELECT  @cnt = @@Rowcount

WHILE @cnt <> 0

BEGIN

      SELECT @dbTmp = dbName from #dbTmp where id = @cnt

           

      SELECT @var = 'INSERT INTO #dbName (dbName, tblName,cols, maxRowSize) '

     

      SELECT @var = @var + 'SELECT ''' + @dbTmp +''', OBJECT_NAME (sc.[object_id]) tablename , COUNT (1) nr_columns , SUM (sc.max_length) maxrowlength FROM '

               +@dbTmp+'.sys.columns sc join ' + @dbTmp + '.sys.objects so on sc.[object_id] = so.[object_id] WHERE so.type = ''U'' GROUP BY OBJECT_NAME (sc.[object_id]) ORDER BY SUM (sc.max_length) desc'

      PRINT @var

     

   EXECUTE (@var)   

      SELECT @cnt =  @cnt - 1      

END

SELECT * FROM #dbName

DROP TABLE #dbTmp

DROP TABLE #dbName

--DB

FindDependenatObjects

FindDependenatObjects

 

--This script can be used to determine the SPs which are using a specific table

CREATE TABLE #tblOut

( id int identity(1,1),

tSchema varchar(100),

dependency varchar(1000)

)

DECLARE @tbl TABLE

( id int identity(1,1),

tSchema varchar(20),

tName varchar(50)

)

DECLARE @schema varchar(20)

DECLARE @table varchar(50)

DECLARE @cnt int = 0

INSERT INTO @tbl (tschema, tname)VALUES

('Controversy','GeneralLedgerDetail')

,('Common','AccountingDocumentItem')

,('Excise','DartAccountingDocumentItem')

,('Controversy','VendorPayment')

,('Common','AccountingDocument')

,('Excise','DartAccountingDocument')

,('Excise','ComplianceMaster')

,('Excise','Invoice')

,('Domestic','SpecialDepreciationDetail')

,('Domestic','FixedAssetDetail')

,('Controversy','GeneralLedgerDetailCount')

,('Controversy','Depreciation')

,('Controversy','AssetMaster')

,('Common','ExcDartUIParams_Hold')

,('Domestic','BSISReport')

,('Common','wrkParADI_YYYYMM')

,('Excise','ReportingJurisdiction')

,('Controversy','VendorAddress')

,('Controversy','GeneralLedgerSummary')

,('Controversy','Vendor')

SELECT @cnt = MAX(id)

FROM @tbl

WHILE (@cnt > 0)

BEGIN

SELECT @schema = tSchema, @table= tName

FROM @tbl

WHERE id = @cnt

INSERT INTO #tblOut (tSchema, dependency)

SELECT @schema + '.' + @table as tbl,

(

SELECT DISTINCT Object_Name(id) + ', '

FROM sys.syscomments

WHERE text like'%'+@schema+'.'+ @table +'%'

OR text like'%'+@schema+'].['+ @table +'%'

OR text like'%'+@schema+'.['+ @table +'%'

OR text like'%'+@schema+'].'+ @table +'%'

FOR xml path('')

)

SELECT @cnt = @cnt -1

END

SELECT * FROM #tblOut

DROP TABLE #tblOut

 

Get Default constraints for all tables across Databases

Get Default constraints for all tables across Databases

CREATE TABLE #dbTmp (id int identity(1,1), dbName nvarchar(200))

CREATE TABLE #dbName (id int identity(1,1), dbName nvarchar(200), ColName nvarchar(200), ConstName nvarchar(200), TableName nvarchar(200))

DECLARE @cnt int = 0

DECLARE @dbTmp varchar(200)

DECLARE @tblTmp varchar(200)

DECLARE @var varchar(1000)

INSERT INTO #dbTmp

SELECT name as dbName from sys.databases where name NOT in ('model', 'master', 'msdb', 'tempdb', 'ReportServer','ReportServerTempDB')

SELECT @cnt = @@Rowcount

WHILE @cnt <> 0

BEGIN

SELECT @dbTmp = dbName from #dbTmp where id = @cnt

SELECT @var = ' USE ' + @dbTmp

PRINT @var

EXECUTE (@var)

SELECT @var = 'INSERT INTO tmpdb..#dbName (dbName, ColName,ConstName, TableName) '

SELECT @var = @var + 'SELECT ''' + @dbTmp +''', c.name as colName , sc.name as ConstName ,t.name as TableName FROM '

+@dbTmp+'.sys.default_constraints sc INNER JOIN '

+@dbTmp+'.sys.tables t on sc.parent_object_id = t.object_id INNER JOIN '

+@dbTmp+'.sys.columns c on t.object_id = c.object_id and sc.parent_column_id = c.column_id '

PRINT @var

EXECUTE (@var)

SELECT @cnt = @cnt - 1

END

SELECT * FROM #dbName

DROP TABLE #dbTmp

DROP TABLE #dbName

--DB

Understanding Disable Index

Understanding Disable Index

-- Create Table

CREATE TABLE [dbo].[TableName](

    [ID] [int] NOT NULL,

    [FirstCol] [varchar](50) NULL,

    CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED ([ID] ASC)

    )

GO

-- Create Nonclustered Index

CREATE UNIQUE NONCLUSTERED INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] ([FirstCol] ASC)

GO

-- Populate Table

INSERT INTO [dbo].[TableName]

SELECT 1, 'First' UNION ALL SELECT 2, 'Second' UNION ALL SELECT 3, 'Third'

GO

-- Disable Nonclustered Index

ALTER INDEX [IX_NonClustered_TableName] ON [dbo].[TableName] DISABLE

GO

-- Insert Data should work fine

INSERT INTO [dbo].[TableName]

SELECT 4, 'Fourth' UNION ALL SELECT 5, 'Fifth' UNION ALL SELECT 6, 'Fifth'

GO

-- Verify Data

Select * from TableName

-- Disable Clustered Index

ALTER INDEX [PK_TableName] ON [dbo].[TableName] DISABLE

GO

-- Insert Data will fail

INSERT INTO [dbo].[TableName] SELECT 7, 'Seventh' UNION ALL SELECT 8, 'Eighth'

GO

/* Error: Msg 8655, Level 16, State 1, Line 1 The query processor is unable to produce a plan because the index 'PK_TableName' on table or view 'TableName' is disabled. */

-- Reorganizing Index will also throw an error

ALTER INDEX [PK_TableName] ON [dbo].[TableName] REORGANIZE

GO

/* Error: Msg 1973, Level 16, State 1, Line 1 Cannot perform the specified operation on disabled index 'PK_TableName' on table 'dbo.TableName'. */

-- Rebuliding should work fine

ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD

GO

-- Insert Data should work fine

INSERT INTO [dbo].[TableName] SELECT 7, 'Seventh' UNION ALL SELECT 8, 'Eighth'

GO

-- Clean Up

DROP TABLE [dbo].[TableName] GO

 

--DB