September 24, 2009 at 1:57 pm
The problem is that I have a lot of "–obe" i.e. something company-obe in compay table validUsers-obe in validUser table and some other -obe colimns in various other table ( approx about 57 columns in various table ), these obsolete columns are obviously never used and are somewhat impeding the performance.
So I would like to know if there is a way that we can select all the –obe columns and delete all the –obe columns rather than having to delte them indivisually with the drop statement .
Now I know how to list them all but I have not been able to comeup with a solution that will drop all the –obe columns.
Could you please guide me as to how this can be accomplished.
Thanks
September 24, 2009 at 2:06 pm
well, you could use something like this:
SELECT 'ALTER TABLE ' +
t.Table_Name +
' drop column ' +
c.Column_Name +
';'
FROM Information_Schema.tables t
INNER JOIN Information_Schema.columns C
ON t.Table_name = c.Table_name
WHERE c.Column_Name LIKE '%-obe'
Even though this is completely untested, and I would not recommend dropping columns in a bulk operation, but whatever your needs are, this might work.
Cheers,
J-F
September 24, 2009 at 2:12 pm
You can't do it in one statement. You could write a script that creates a script for you like:
DECLARE @sql NVARCHAR(4000)
SELECT
@sql = COALESCE(@sql, N'') + N'Alter Table ' + S.[name] + N'.' + T.[name] + ' Drop Column ' + AC.NAME + NCHAR(10)
FROM
sys.all_columns AS AC JOIN
sys.tables AS T ON AC.[object_id] = T.[object_id] JOIN
sys.schemas AS S ON T.[schema_id] = S.[schema_id]
WHERE
AC.[name] LIKE '%-obe'
SELECT @sql
Always test first and make sure you have a good backup before dropping any columns in production.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2009 at 2:23 pm
Thanks that works,thanK you so much
thers is somehting else i am struggling with While upsizing from access to SQL all the IsActive fields that are of bit type are upsized with null not allowed and we need them to allow null is there a script that would do this and also the memo fields on access are trasnferred as ntext can we change them all with a script to nvarchar(MAX),i.e all the columns IsActive Columns are set to Allow null and all the ntext fields are set as nvarchar(max)
As of now i have been doing the alter one at a time
thanks again
December 2, 2009 at 3:17 am
Just have same problem
thanks too
use
while EXISTS (SELECT * FROM sys.columns where [name] LIKE '%-obe')
begin
DECLARE @sql NVARCHAR(4000)
SELECT
@sql = N'Alter Table ' + T.[name] + N' Drop Column ' + C.NAME
FROM
sys.columns AS C JOIN
sys.tables AS T ON C.[object_id] = T.[object_id]
WHERE
C.[name] LIKE '%-obe';
exec sp_executesql @sql;
end
It droops all columns in DB
December 2, 2009 at 11:49 pm
avi-631555 (9/24/2009)
Thanks that works,thanK you so muchthers is somehting else i am struggling with While upsizing from access to SQL all the IsActive fields that are of bit type are upsized with null not allowed and we need them to allow null is there a script that would do this and also the memo fields on access are trasnferred as ntext can we change them all with a script to nvarchar(MAX),i.e all the columns IsActive Columns are set to Allow null and all the ntext fields are set as nvarchar(max)
As of now i have been doing the alter one at a time
thanks again
I dont know why you want to make a bit column nullable, but here is a script similar to the one posted in the earlier post.
DECLARE @sql NVARCHAR(4000)
SELECT
@sql = COALESCE(@sql, N'') + N'Alter Table ' + T.[TABLE_NAME]+ ' ALTER Column '
+ T.COLUMN_NAME + ' ' + T.DATA_TYPE + ' NULL ' +
NCHAR(10)
FROM
information_schema.columns T
WHERE
T.[COLUMN_NAME] LIKE 'IsActive%' -- Put the correct where condition here
SELECT @sql
"Keep Trying"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply