When running the below SQL Query I keep getting the following error:
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table 'zContractDefault'.
For some reason it is only returning the first 11 chars of the column name?
This query searches a DB and determines which columns are 'Invalid' if the column name is >30 and contains '_T':
USE PM7Database
GO
--NOTE: Display to user a list of bad columns in the result set.
Select c.table_name, c.column_name, charindex('_T', c.column_name) as Char_Index, substring(c.column_name, charindex('_T', c.column_name), 100) as Bad_Col_Portion, len(substring(c.column_name, charindex('_T', c.column_name), 100)) as Bad_Col_Port_Length
from information_schema.columns as c
Inner Join
information_schema.tables as t
ON
c.table_name = t.table_name
where t.table_type <> 'VIEW'
and
len(substring(c.column_name, charindex('_T', c.column_name), 100)) > 30
and
charindex('_T', c.column_name) <> 0
order by 1, 2
--NOTE: Display a count of all columns in the database.
-- Used to help validate that only appropriate fields were removed.
Select 'Column Count of all Columns in the Database: ' + Convert(Varchar(50), Count(*)) as 'Comment....................................................Comment'
from information_schema.columns
-- SCRIPT SETUP!
Declare @Specific_Col varchar(100)
Declare @Specific_Table varchar(100)
Declare @Spec_Col_Eval varchar(100)
Declare @Char_Index_Eval int
Declare @Spec_Tbl_Eval varchar(100)
Declare @DropStmt varchar(2000)
Declare @RowCount int
Declare @RowIncrement int
set @RowIncrement = 0
-- Table Variable setup to capture some of the process that goes on with this script.
Declare @TableResults Table
(
DropStmt varchar(2000),
DropStmtLength int
)
-- Worker Table Variable used to capture from the database all
Declare @TableInspect Table
(
[Database] varchar(70),
Table_name varchar(70),
Column_name varchar(100),
Char_Index int
)
Insert into @TableInspect
Select c.table_catalog, c.table_name, c.column_name, charindex('_T', c.column_name)
from information_schema.columns as c
Inner Join
information_schema.tables as t
ON
c.table_name = t.table_name
where t.table_type <> 'VIEW'
and
len(substring(c.column_name, charindex('_T', c.column_name), 100)) > 30
and
charindex('_T', c.column_name) <> 0
order by 1 desc, 2
-- Set a counter for # of columns to drop.
-- Used for reporting AND required to make sure the last columns are actually dropped.
Select @RowCount = Count(*) from @TableInspect as [Comment....................................................Comment]
-- Lets the user know how many columns are actually found and need to be dropped.
-- If UDF Fields are present, they will not be dropped but will be added to the counter.
Select 'Total Columns to Drop: ' + Convert(Varchar(100), @RowCount) as [Comment....................................................Comment]
-- Cursor used to evaluate column names as completely valude to drop and set
-- up a drop statement to remove the columns. Pulls data from the Table Variable
-- that was declared above.
declare ColumnSearcher cursor for
Select Table_Name, Column_Name, Char_Index
from @TableInspect
set nocount on
open ColumnSearcher
-- These 3 variables used by the fetch are required in the evaluations below.
fetch next from ColumnSearcher into @Specific_Table, @Specific_Col, @Char_Index_Eval
while @@fetch_status = 0
-- Begin Main search for erroneous columns.
Begin
-- This conditional statement ensures that that the Drop statement is prepared to be run.
-- Requirements for Drop Statement to run are:
-- Req: The Currently Fetched table must be a different name than the last fetched table.
-- Reason: Allows less actual hits to the database for columns to be dropped. Each Drop Statement
-- can contain 2000 characters. This allows for roughly 40 columns to be dropped from a single table.
-- That estimate is based on an average Column size using the following equation:
-- 15 (high avg. base column name size) + 34 (constant size of the erroneous column) = 49
-- 2000/49 = 40.8... round down to 40.
-- This is a limitation.
-- Req: The Last Fetched Table and the Currently Fetched table cannot be Null. If so then it is likely
-- the first row in the fetch. This is wehre @Spec_Tbl_Eval gets first assigned. By a null value of
-- the first row.
If @Spec_Tbl_Eval <> @Specific_Table and @Spec_Tbl_Eval IS NOT NULL and @Specific_Table IS NOT NULL
Begin
Set @DropStmt = 'Use ' + db_name() + ' ' + @DropStmt
-- Log into the @TableResults table variable the results of what the drop statement actually is dropping.
Insert @TableResults
Values (@DropStmt, len(@dropstmt))
exec(@DropStmt)
-- Force Drop Statement to be NULL every time it is executed. This allows a change to a different table
-- for every drop that is submitted to SQL Server.
Set @DropStmt = NULL
Set @Spec_Tbl_Eval = @Specific_Table
End
else
Set @Spec_Tbl_Eval = @Specific_Table
-- This conditional statement is used to build the complete drop statement.
If @Char_Index_Eval <> 0 and @Char_Index_Eval IS NOT NULL
Begin
-- If the @DropStmt variable is null, then a beginning to the drop statement must first be added.
if @DropStmt IS Null
Begin
-- This block sets the beginning of the drop statement and evalutes the @Specific_Col which has been
-- deemed droppable to make sure it is not a UDF field.
if substring(@Specific_Col, 1, 4) <> 'udf_'
Begin
Set @DropStmt = 'Alter Table ' + @Specific_Table + ' Drop Column ' + @Specific_Col
Set @RowIncrement = @RowIncrement + 1
End
Else
-- Forces notification regarding UDF fields.
Insert @TableResults
Values ('UDF COLUMN SKIPPED - ' + @Specific_Col, 0)
Set @RowIncrement = @RowIncrement + 1
End
Else
Begin
-- This block adds onto an NOT NULL @DropStmt variable extra columns that need to be dropped from a specific
-- table.
Set @DropStmt = @DropStmt + ', ' + @Specific_Col
Set @RowIncrement = @RowIncrement + 1
End
End
-- Check for last drop statement and make sure it is executed.
If @RowIncrement = @RowCount
Begin
Set @DropStmt = 'Use ' + db_name() + ' ' + @DropStmt
exec(@DropStmt)
Set @DropStmt = NULL
Set @Spec_Tbl_Eval = NULL
End
fetch next from ColumnSearcher into @Specific_Table, @Specific_Col, @Char_Index_Eval
End
close ColumnSearcher
deallocate ColumnSearcher
-- Display the results of the @TableResults Table to the customer. Usefull for troubleshooting missing columns.
Select * from @TableResults
-- Final count of all columns with in the database. Note that UDF Fields have not been dropped so it may
-- appear that you have additional columns that still remain. Make sure to subtract from this number any
-- left over UDF Columns which you can find by looking at the @TableResults.
Select 'Total Columns Remaining: ' + Convert(Varchar(100), Count(*)) as 'Comment....................................................Comment'
from information_schema.columns
Server: Msg 4924, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table 'zContractDefault'.
For some reason it is only returning the first 11 chars of the column name?
This query searches a DB and determines which columns are 'Invalid' if the column name is >30 and contains '_T':
USE PM7Database
GO
--NOTE: Display to user a list of bad columns in the result set.
Select c.table_name, c.column_name, charindex('_T', c.column_name) as Char_Index, substring(c.column_name, charindex('_T', c.column_name), 100) as Bad_Col_Portion, len(substring(c.column_name, charindex('_T', c.column_name), 100)) as Bad_Col_Port_Length
from information_schema.columns as c
Inner Join
information_schema.tables as t
ON
c.table_name = t.table_name
where t.table_type <> 'VIEW'
and
len(substring(c.column_name, charindex('_T', c.column_name), 100)) > 30
and
charindex('_T', c.column_name) <> 0
order by 1, 2
--NOTE: Display a count of all columns in the database.
-- Used to help validate that only appropriate fields were removed.
Select 'Column Count of all Columns in the Database: ' + Convert(Varchar(50), Count(*)) as 'Comment....................................................Comment'
from information_schema.columns
-- SCRIPT SETUP!
Declare @Specific_Col varchar(100)
Declare @Specific_Table varchar(100)
Declare @Spec_Col_Eval varchar(100)
Declare @Char_Index_Eval int
Declare @Spec_Tbl_Eval varchar(100)
Declare @DropStmt varchar(2000)
Declare @RowCount int
Declare @RowIncrement int
set @RowIncrement = 0
-- Table Variable setup to capture some of the process that goes on with this script.
Declare @TableResults Table
(
DropStmt varchar(2000),
DropStmtLength int
)
-- Worker Table Variable used to capture from the database all
Declare @TableInspect Table
(
[Database] varchar(70),
Table_name varchar(70),
Column_name varchar(100),
Char_Index int
)
Insert into @TableInspect
Select c.table_catalog, c.table_name, c.column_name, charindex('_T', c.column_name)
from information_schema.columns as c
Inner Join
information_schema.tables as t
ON
c.table_name = t.table_name
where t.table_type <> 'VIEW'
and
len(substring(c.column_name, charindex('_T', c.column_name), 100)) > 30
and
charindex('_T', c.column_name) <> 0
order by 1 desc, 2
-- Set a counter for # of columns to drop.
-- Used for reporting AND required to make sure the last columns are actually dropped.
Select @RowCount = Count(*) from @TableInspect as [Comment....................................................Comment]
-- Lets the user know how many columns are actually found and need to be dropped.
-- If UDF Fields are present, they will not be dropped but will be added to the counter.
Select 'Total Columns to Drop: ' + Convert(Varchar(100), @RowCount) as [Comment....................................................Comment]
-- Cursor used to evaluate column names as completely valude to drop and set
-- up a drop statement to remove the columns. Pulls data from the Table Variable
-- that was declared above.
declare ColumnSearcher cursor for
Select Table_Name, Column_Name, Char_Index
from @TableInspect
set nocount on
open ColumnSearcher
-- These 3 variables used by the fetch are required in the evaluations below.
fetch next from ColumnSearcher into @Specific_Table, @Specific_Col, @Char_Index_Eval
while @@fetch_status = 0
-- Begin Main search for erroneous columns.
Begin
-- This conditional statement ensures that that the Drop statement is prepared to be run.
-- Requirements for Drop Statement to run are:
-- Req: The Currently Fetched table must be a different name than the last fetched table.
-- Reason: Allows less actual hits to the database for columns to be dropped. Each Drop Statement
-- can contain 2000 characters. This allows for roughly 40 columns to be dropped from a single table.
-- That estimate is based on an average Column size using the following equation:
-- 15 (high avg. base column name size) + 34 (constant size of the erroneous column) = 49
-- 2000/49 = 40.8... round down to 40.
-- This is a limitation.
-- Req: The Last Fetched Table and the Currently Fetched table cannot be Null. If so then it is likely
-- the first row in the fetch. This is wehre @Spec_Tbl_Eval gets first assigned. By a null value of
-- the first row.
If @Spec_Tbl_Eval <> @Specific_Table and @Spec_Tbl_Eval IS NOT NULL and @Specific_Table IS NOT NULL
Begin
Set @DropStmt = 'Use ' + db_name() + ' ' + @DropStmt
-- Log into the @TableResults table variable the results of what the drop statement actually is dropping.
Insert @TableResults
Values (@DropStmt, len(@dropstmt))
exec(@DropStmt)
-- Force Drop Statement to be NULL every time it is executed. This allows a change to a different table
-- for every drop that is submitted to SQL Server.
Set @DropStmt = NULL
Set @Spec_Tbl_Eval = @Specific_Table
End
else
Set @Spec_Tbl_Eval = @Specific_Table
-- This conditional statement is used to build the complete drop statement.
If @Char_Index_Eval <> 0 and @Char_Index_Eval IS NOT NULL
Begin
-- If the @DropStmt variable is null, then a beginning to the drop statement must first be added.
if @DropStmt IS Null
Begin
-- This block sets the beginning of the drop statement and evalutes the @Specific_Col which has been
-- deemed droppable to make sure it is not a UDF field.
if substring(@Specific_Col, 1, 4) <> 'udf_'
Begin
Set @DropStmt = 'Alter Table ' + @Specific_Table + ' Drop Column ' + @Specific_Col
Set @RowIncrement = @RowIncrement + 1
End
Else
-- Forces notification regarding UDF fields.
Insert @TableResults
Values ('UDF COLUMN SKIPPED - ' + @Specific_Col, 0)
Set @RowIncrement = @RowIncrement + 1
End
Else
Begin
-- This block adds onto an NOT NULL @DropStmt variable extra columns that need to be dropped from a specific
-- table.
Set @DropStmt = @DropStmt + ', ' + @Specific_Col
Set @RowIncrement = @RowIncrement + 1
End
End
-- Check for last drop statement and make sure it is executed.
If @RowIncrement = @RowCount
Begin
Set @DropStmt = 'Use ' + db_name() + ' ' + @DropStmt
exec(@DropStmt)
Set @DropStmt = NULL
Set @Spec_Tbl_Eval = NULL
End
fetch next from ColumnSearcher into @Specific_Table, @Specific_Col, @Char_Index_Eval
End
close ColumnSearcher
deallocate ColumnSearcher
-- Display the results of the @TableResults Table to the customer. Usefull for troubleshooting missing columns.
Select * from @TableResults
-- Final count of all columns with in the database. Note that UDF Fields have not been dropped so it may
-- appear that you have additional columns that still remain. Make sure to subtract from this number any
-- left over UDF Columns which you can find by looking at the @TableResults.
Select 'Total Columns Remaining: ' + Convert(Varchar(100), Count(*)) as 'Comment....................................................Comment'
from information_schema.columns