question

srinnippu-1270 avatar image
0 Votes"
srinnippu-1270 asked srinnippu-1270 commented

Get the _skey column name from table and Reset the value of key column to 1 when reaches to max from table on every run of sp

Hi,

I am writing the below stored procedure to get _skey column from all tables present in DBO Schema and reset _skey column to 1 on every run of my storted procudere.


CREATE PROCEDURE sp_reset_1
(
@TableName SYSNAME
, @IdentityColumn SYSNAME
)
AS
BEGIN
DECLARE @SQLCommand NVARCHAR(4000)
SET @SQLCommand = N'
DECLARE @MaxID INT;
SET @MaxID = COALESCE((SELECT MAX(' + QUOTENAME(@IdentityColumn) + ') FROM ' + QUOTENAME(@TableName) + '), 1);
DBCC CHECKIDENT(' + QUOTENAME(@TableName, '''') + ', RESEED, @MaxID);
';

--EXEC sp_executesql @SQLCommand;
END

Please suggest how to write/modify the above sp to get the _skey column from all tables dynmically having DBO Scheama.kindly suggest.

Thanks

azure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AnuragSharma-08 avatar image
0 Votes"
AnuragSharma-08 answered srinnippu-1270 commented

Hi @srinnippu-1270, welcome to Microsoft Q&A forum.

Please find the Stored Procedure below:

//Updated the SP as per suggestion

 CREATE PROCEDURE sp_ResetIdentityMax
  AS
  DECLARE @Table TABLE
  (
  TableName VARCHAR(50),
  Id int identity(1,1)
  )
        
  --Getting only identity columns from all tables with dbo schema. You can add column name as well in where condition if you would like to
  INSERT INTO @Table
  select TABLE_NAME  from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and COLUMN_NAME like '%SKey' AND COLUMN_NAME NOT LIKE '%LOAD_SKey' AND COLUMN_NAME NOT LIKE 'GDP%'
        
  DECLARE @max int, @SQL VARCHAR(MAX) , @TableName VARCHAR(100), @ColumnName varchar(100)
  DECLARE @id int = 1
  select @max = MAX(Id) from @Table
        
  WHILE (@id <= @max)
  BEGIN
        
  SELECT @TableName = TableName FROM @Table WHERE Id = @id
        
  --Resetting the column value to '1' for new records
  DBCC CHECKIDENT(@TableName, RESEED,0);
        
  SET @id = @id +1
  END

Referenced Article: t-sql for loop to iterate through tables

Please let me know if this helps or else we can discuss further


If answer helps, you can mark it 'Accept Answer'





· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Anurag,

Thanks for replying..

I am looking for Add below conditions to pick from 'dbo' scheama & I have to RESEED the Column name Based on _SKey in every table ( sys.columns.name LIKE '%_SKey' ) when i use the condition LIKE '%SKey other columns also picking for SEEDING ,I dont want other columns which and i don't want include column name starts with LOAD_SKey.
Also i need to aviod tables from the same schema table names starts with 'GDP
%'

psedo code below :

schema.name = 'dbo'
AND sys.columns.name LIKE '%SKey'
AND sys.columns.name NOT LIKE '%LOAD_SKey'
AND sys.tables.name NOT LIKE 'GDP
%'


Please suggest How we can include these conditions in

0 Votes 0 ·

Thanks for replying back, I have updated the Stored procedure in first reply to include these conditions as well.

For reference this is the part that I updated. You can run below statement directly and check if all valid tables are retrieved.

 select TABLE_NAME  from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and COLUMN_NAME like '%SKey' AND COLUMN_NAME NOT LIKE '%LOAD_SKey' AND COLUMN_NAME NOT LIKE 'GDP%'



0 Votes 0 ·

Thanks for suggestion.


select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 and COLUMN_NAME like '%SKey' AND COLUMN_NAME NOT LIKE '%LOAD_SKey' AND COLUMN_NAME NOT LIKE 'GDP%'

when i ran the above Query i am not getting any data,After excluding COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 ,I am able to get the data.

Please suggest its expected behaviour.

0 Votes 0 ·
Show more comments