Removal of Records dynamically using Information Schema

• Jonathan Marshall

  SSCrazy

  Points: 2633

  More actions

  August 14, 2015 at 5:33 am

  #303474

  Hello everyone,

  I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server.

  Looking to remove records from all the tables within a schema.

  I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.

  DECLARE@TargetSchema varchar(100),

  --@LibNameData varchar(100),

  @fnameIndex varchar(100),

  --@startOFR_SCR_FILENAME_DATE varchar(25),

  --@endOFR_SCR_FILENAME_DATE varchar(25),

  @DataSetName varchar(100),

  @databaseName NVARCHAR(max),

  --SET @DataSetName = 'SSN'

  @DqzDateVerTime AS NVARCHAR(14),

  --SET @DqzDateVerTime = GETDATE()

  @TableName varchar(100)

  SET @TargetSchema = 'dbo'

  SET @DqzDateVerTime = '20150813121500'

  SET @DataSetName = 'SSN'

  SET @databaseName = 'LOU_LEI'

  DECLARE @PurgeDataSet2 varchar(max)

  SET @PurgeDataSet2=''SELECT

  'DELETE FROM ' + @databaseName + '.' + @TargetSchema + '.' + TABLE_NAME + ' WHERE DqzDateVerTime =''' + @DqzDateVerTime + '' + ''' AND dataset_name=''' + @DataSetName + ''';'

  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='' + @TargetSchema + '' AND TABLE_NAME != 'systranschemas'

  --PRINT 'DELETE FROM ' + @PurgeDataSet2

  --EXEC (@PurgeDataSet2)

  WHILE EXISTS

  (

  SELECT

  'SELECT * FROM ' + @databaseName + '.' + @TargetSchema + '.' + TABLE_NAME + ' WHERE DqzDateVerTime =''' + @DqzDateVerTime + '' + ''' AND dataset_name=''' + @DataSetName + ''';'

  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='' + @TargetSchema + '' AND TABLE_NAME != 'systranschemas'

  )

  BEGIN

  ---PRINT 'HELLO'

  EXEC(@PurgeDataSet2)

  END

  Statements generated from information schema:

  DELETE FROM LOU_LEI.dbo.tableName1 WHERE DqzDateVerTime ='20150813121500' AND dataset_name='SSN';

  DELETE FROM LOU_LEI.dbo.tableName2 WHERE DqzDateVerTime ='20150813121500' AND dataset_name='SSN';

  Would like to execute the statements generated by the results from the information schema.

  I'm stuck at this point.

  Any suggestions and or other approaches?

  Regards,

  Jonathan

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 14, 2015 at 7:03 am

  #1820030

  Jonathan Marshall (8/14/2015)

  ---

  Hello everyone,

  I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server.

  Looking to remove records from all the tables within a schema.

  I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.

  DECLARE@TargetSchema varchar(100),

  --@LibNameData varchar(100),

  @fnameIndex varchar(100),

  --@startOFR_SCR_FILENAME_DATE varchar(25),

  --@endOFR_SCR_FILENAME_DATE varchar(25),

  @DataSetName varchar(100),

  @databaseName NVARCHAR(max),

  --SET @DataSetName = 'SSN'

  @DqzDateVerTime AS NVARCHAR(14),

  --SET @DqzDateVerTime = GETDATE()

  @TableName varchar(100)

  SET @TargetSchema = 'dbo'

  SET @DqzDateVerTime = '20150813121500'

  SET @DataSetName = 'SSN'

  SET @databaseName = 'LOU_LEI'

  DECLARE @PurgeDataSet2 varchar(max)

  SET @PurgeDataSet2=''SELECT

  'DELETE FROM ' + @databaseName + '.' + @TargetSchema + '.' + TABLE_NAME + ' WHERE DqzDateVerTime =''' + @DqzDateVerTime + '' + ''' AND dataset_name=''' + @DataSetName + ''';'

  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='' + @TargetSchema + '' AND TABLE_NAME != 'systranschemas'

  --PRINT 'DELETE FROM ' + @PurgeDataSet2

  --EXEC (@PurgeDataSet2)

  WHILE EXISTS

  (

  SELECT

  'SELECT * FROM ' + @databaseName + '.' + @TargetSchema + '.' + TABLE_NAME + ' WHERE DqzDateVerTime =''' + @DqzDateVerTime + '' + ''' AND dataset_name=''' + @DataSetName + ''';'

  FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='' + @TargetSchema + '' AND TABLE_NAME != 'systranschemas'

  )

  BEGIN

  ---PRINT 'HELLO'

  EXEC(@PurgeDataSet2)

  END

  Statements generated from information schema:

  DELETE FROM LOU_LEI.dbo.tableName1 WHERE DqzDateVerTime ='20150813121500' AND dataset_name='SSN';

  DELETE FROM LOU_LEI.dbo.tableName2 WHERE DqzDateVerTime ='20150813121500' AND dataset_name='SSN';

  Would like to execute the statements generated by the results from the information schema.

  I'm stuck at this point.

  Any suggestions and or other approaches?

  Regards,

  Jonathan

  Why do you need to execute a dynamically generated select statement? You are 99.9999% there. Just change your dynamic sql to delete.

  BTW, the INFORMATION_SCHEMA views are not totally reliable. They just exist for backwards compatibility. You are using that views to determine the schema of your tables which is not reliable. https://msdn.microsoft.com/en-us/library/ms186224.aspx You should just use sys.tables instead.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 14, 2015 at 7:50 am

  #1820052

  Small correction, they're not there for backwards compatibility, but for SQL Standard compatibility.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 14, 2015 at 7:54 am

  #1820055

  Luis Cazares (8/14/2015)

  ---

  Small correction, they're not there for backwards compatibility, but for SQL Standard compatibility.

  Thanks for the correction Luis. 😉

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 14, 2015 at 8:50 am

  #1820085

  Luis Cazares (8/14/2015)

  ---

  Small correction, they're not there for backwards compatibility, but for SQL Standard compatibility.

  True, but not every RDBMS implements them either. What good is a standard not used? Plus, you can get a lot more useful information from the system views like sys.tables, sys.columns, etc.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 14, 2015 at 9:27 am

  #1820104

  Lynn Pettis (8/14/2015)

  ---

  Luis Cazares (8/14/2015)

  ---

  Small correction, they're not there for backwards compatibility, but for SQL Standard compatibility.

  True, but not every RDBMS implements them either. What good is a standard not used? Plus, you can get a lot more useful information from the system views like sys.tables, sys.columns, etc.

  I agree, the SQL standard is a vaguely used standard, but it helps us to keep a common path. The proprietary system views will give more information and are more reliable, but information schema views give basic information easily. Both have pros and cons.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 14, 2015 at 10:14 am

  #1820122

  Luis Cazares (8/14/2015)

  ---

  Lynn Pettis (8/14/2015)

  ---

  Luis Cazares (8/14/2015)

  ---

  Small correction, they're not there for backwards compatibility, but for SQL Standard compatibility.

  True, but not every RDBMS implements them either. What good is a standard not used? Plus, you can get a lot more useful information from the system views like sys.tables, sys.columns, etc.

  I agree, the SQL standard is a vaguely used standard, but it helps us to keep a common path. The proprietary system views will give more information and are more reliable, but information schema views give basic information easily. Both have pros and cons.

  I'm using SQL Server so I'll use the system views provided by SQL Server. If the INFORMATION_SCHEMA views were more reliable and used more universally by other RDBMS I'd consider using them.