Wednesday, 28 November 2012

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 

Get PK column Name for a table

Get PK column Name for a table

DECLARE @destTbl varchar(100) = 'Account'
 SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
   FROM sys.indexes AS i
  INNER JOIN sys.index_columns AS ic
        ON i.OBJECT_ID = ic.OBJECT_ID
        AND i.index_id = ic.index_id
  WHERE i.is_primary_key = 1
    AND OBJECT_NAME(ic.OBJECT_ID) = @destTbl



--DB