maintenance-tasks

Generate row number column in SQL Server

Sometimes we need our SELECT statement to return current row number. In the following script it is shown two ways you can perform this task.

-- Sample 1: Using SELF JOIN
SELECT
        (SELECT COUNT(TerritoryID)
        FROM dbo.Territories T2
        WHERE T2.TerritoryID <= T1.TerritoryID) AS RowNumber
        , T1.*
FROM dbo.Territories T1

-- Sample 2: Using IDENTITY
SELECT
    IDENTITY(int,1,1) as RowNumber, *
    INTO #TempTerritories
FROM dbo.Territories

SELECT * FROM #TempTerritories
DROP TABLE #TempTerritories

Anyway, most of the times you can avoid this practice, and build row number in the GUI part. By example, if you have a report and you want to add a row count column, you can insert a special field called “Record Number”: select Insert menu/Special Field/RecordNumber.

Your suggestions are welcomed.

Empty all tables in SQL Server

In day-by-day programmer life you need a magic script to clean up data you’ve inserted in the application you work on. Of course, you can do this by running ‘CREATE TABLE’ statements and recreate your database from scratch. This is perfect valid, but most of the times you want to keep data in some tables like: configuration tables, catalogues tables, so recreating database is not appropriate in this case.

The following script, interrogates sysobjects table to get all user tables. Then it tries to delete rows from tables. Some of the tables’ rows could be deleted most probably because of reference integrity will be broken. If so, I reiterate all tables and retrying to delete rows. This operation continues until no error is raised while deleting rows from all tables. I must admit this solution uses brute- force, but I found it very simple. I’ve tested it on medium size databases and the results were very good.

If you need to keep data in some tables, you just need to add a clause to the SQL statement getting all user tables in current database:
AND name NOT IN ('author', 'titles', ‘table X’)

SET NOCOUNT ON
DECLARE @TABLE_NAME NVARCHAR(255), @HAS_IDENTITY NUMERIC(15), @HAS_ERROR BIT

-- Create a cursor with all table names
DECLARE TABLES_CURSOR CURSOR SCROLL FOR
SELECT name
FROM sysobjects
WHERE xtype = 'U' AND name <> 'dtproperties'
--AND name NOT IN ('author', 'titles', ...)

SET @HAS_ERROR = 1
OPEN TABLES_CURSOR
WHILE @HAS_ERROR <> 0
BEGIN
       SET @HAS_ERROR = 0
       FETCH FIRST FROM TABLES_CURSOR INTO @TABLE_NAME
       WHILE @@FETCH_STATUS = 0
       BEGIN        
             EXECUTE ('DELETE ' + @TABLE_NAME)
             IF @@ERROR<>0
                    -- Table rows can't be deleted.
                    SET @HAS_ERROR = 1
             ELSE
                    BEGIN
                           --Reset identity for emptied table
                           SET @HAS_IDENTITY = (SELECT IDENT_CURRENT(@TABLE_NAME))
                           IF @HAS_IDENTITY IS NOT NULL
                           BEGIN
                                 DBCC CHECKIDENT (@TABLE_NAME, reseed, 0) WITH NO_INFOMSGS
                           END
                    END

             FETCH NEXT FROM TABLES_CURSOR INTO @TABLE_NAME
       END
END

CLOSE TABLES_CURSOR
DEALLOCATE TABLES_CURSOR

Your suggestions are welcomed.

SQL Server: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

You cannot truncate a table which has an FK constraint on it. As workaround, you could:
- Drop the constraints.
- Trunc the table.
- Recreate the constraints.

Here it is the associated T-SQL script, supposing you have 2 tables called MyTable and MyReferencedTable:

-- Remove constraint
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable')
BEGIN
        ALTER TABLE dbo.MyReferencedTable
        DROP CONSTRAINT FK_MyReferencedTable_MyTable
END


-- Truncate table
TRUNCATE TABLE dbo.MyTable


-- Re-Add constraint
IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable')
BEGIN
        ALTER TABLE dbo.MyReferencedTable
        WITH CHECK ADD CONSTRAINT [FK_MyReferencedTable_MyTable] FOREIGN KEY(ListingKey)
        REFERENCES dbo.MyTable (ListingKey)
END

Subscribe to RSS - maintenance-tasks