Search

Advertisements

Showing posts with label DBCC Commands. Show all posts
Showing posts with label DBCC Commands. Show all posts

T-SQL - RESEED identity if the table has zero records

The below code reseeds identity value if the table has zero record count.

--Temp tables to hold tablenames & recordcounts

CREATE TABLE ##TableRowCounts([TableName] VARCHAR(128), [RowCount] INT) ;
CREATE TABLE ##TableNoRowCounts(RN INT IDENTITY(1,1),[TableName] VARCHAR(128), [RowCount] INT) ;

INSERT INTO ##TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;

INSERT ##TableNoRowCounts
SELECT [TableName], [RowCount] FROM ##TableRowCounts
WHERE [RowCount] = 0

DECLARE @i TINYINT = 1,
        @query NVARCHAR(MAX),
        @tblname NVARCHAR(MAX)

WHILE @i <= (SELECT COUNT(1) FROM ##TableNoRowCounts)

BEGIN

SET @tblname = (SELECT [TableName] FROM ##TableNoRowCounts WHERE RN = @i)
SET @tblname = REPLACE(REPLACE(REPLACE(@tblname,'[dbo].',''),']',''),'[','')
--RESEED Identity
SET @query = 'DBCC CHECKIDENT ('+@tblname+', RESEED, 0)'
EXEC (@query)
SET @i = @i + 1

END

--Dropping temp tables
DROP TABLE ##TableRowCounts
DROP TABLE ##TableNoRowCounts 
 


Reference :http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/0f0d17ac-1db9-4599-9852-cc3628f169e5
 

SQL Server - DBCC PAGE and DBCC IND



DBCC IND – used to know the page details of tables & indexes.

syntax:
DBCC IND ( {dbname}, {table_name},{index_id} )
Description of columns displayed from DBCC IND

PageFID
File ID of Page

PagePID
Page ID

IAMFID,IAMPID
File ID&Page ID -> IAM mapping


ObjectID
 object ID(Table)

IndexID
Clustered index (IndexID = 1),Non clustered index (IndexID = 2)

PartitionNumber
 Number of Partition which holds data & index pages

PartitionID
ID of Partition which holds data & index pages

iam_chain_type
In-row data,Row-Overflow data

PageType
1=Data page,2=Index page,3&4=Text page,10=IAM

IndexLevel
0=leaf level

NextPageFID,NextPagePID,PrevPageFID,PrevPagePID
Next & Previous Page ID 's & File ID 's of a page




 

DBCC PAGE – used to see the contents of  a Page.

syntax:
DBCC  PAGE( {dbid|dbname}, pagenum [,print option] [,cache] [,logical] )



DBCC PAGE & DBCC IND both are undocumented commands.

Few examples for DBCC PAGE & DBCC IND:

Advertisements