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

Hi @Raj0125 , 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'

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

Accepted answer

Anurag Sharma 17,571 Reputation points

2022-05-25T08:00:13.757+00:00
Hi @Raj0125 , 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'
Please sign in to rate this answer.
Raj0125 511 Reputation points

2022-05-25T08:52:09.83+00:00

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

Anurag Sharma 17,571 Reputation points

2022-05-25T09:03:08.737+00:00

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%'

Raj0125 511 Reputation points

2022-05-25T10:08:39.91+00:00

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.

Anurag Sharma 17,571 Reputation points

2022-05-25T10:18:19.233+00:00

Below condition brings the data only when column is defined as identity column. As an example I created a new table:

create table identityTable (id int identity(1,1), name varchar(100))

Then if I run below query, it will return the record:

select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = 'dbo' and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

Output:

This statement should work for you as well if identity columns are defined in tables. Otherwise if there are no identity columns in the table, running DBCC CHECKIDENT(@TableName, RESEED,0); will also not make any difference.

Can you please provide the ddl script of any table that has identity columns and I can check whats the issue there?

Raj0125 511 Reputation points

2022-05-25T14:26:34.15+00:00

Thanks for response

I am providing DDL Script for one of the table

CREATE TABLE [DBO].[Type_Look_BKP](
[Type_Look_SKey] [int] NOT NULL,
[Type_Look_Id] varchar NULL,
[LK_Natural_Key] varchar NULL,
[LK_Code] varchar NULL,
[LK_Description] varchar NULL,
[Load_SKey] [int] NULL

There is no Identity column in my tables.
Here first column Type_Look_SKey i am consider in sp to reset to 1 from its maximum value when ever our sp will run.Becuasev otherwise it reaches maximum value 2147483647 and we are getting Arthametic error.

My intention is avoid the error by reset the value to 0 so that next insert will start from 1 and increses so on.. Every 3 months when i run the sp to reset the Type_Look_SKey column to 0.

so that i am taking _SKey column but when i see some tables having more than one _Columns ended with __SKey column.To avoid this i have to select 1 st column from evry table ideally first column from every table is always _SKey.

Please assist.

Raj0125 511 Reputation points

2022-05-26T06:06:48.913+00:00

So ihave Modified your SP supplied as below

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 distinct 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 TABLE_NAME NOT LIKE 'GDP_%'
AND ordinal_position = 1 AND table_name = ' [DBO].[Type_Look_BKP]'
DECLARE @Max int, @alenzi 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

** commented to get Identidity column
** AND Included ordinal_position = 1 in your code additionally But i am getting 0 rows effected.

Even i trired with table_name with Identity column in the above SP but with out uncomment the line and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 i am not getting data for that sleect.

Thanks

Please assit

Anurag Sharma 17,571 Reputation points

2022-05-26T06:27:22.093+00:00

ordinal_position = 1 is going to bring the first column in the table, but I am not sure why are you using below condition.

table_name = ' [DBO].[Type_Look_BKP]'

Firstly we dont need to use schema name in this condition, so this could be simply:

table_name = 'Type_Look_BKP

and then if we put this condition only this table will be retrieved.

Raj0125 511 Reputation points

2022-05-26T06:56:33.287+00:00

Hi Anurag,

I hard coded table for testing purpose.
In some tables we had Identical columns even though if we use those table having Identical column not bringing any result

Thank You.
Sign in to comment

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

0 additional answers