SQL 2012 :: How To Drop Columns From Alter View

Mar 20, 2015

I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.

Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .

The view is called TEMP_EXPORT_1

I can either use the code below to return a list of columns that I want removed:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'

Or I can use the code below to return the list of columns that I want to keep:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'

Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...

Therefore I tried the following but I'm not sure of the syntax:

ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1

Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.

View 2 Replies


ADVERTISEMENT

Using Alter Table To Drop Columns

Nov 5, 2000

I am dropping 60 out of 133 columns on a user table, but am finding that the spaceused by the table ( as shown by sysindexes.dpages ) is not being freed up.
I have dropped and recreated all indexes on the table, with no effect on the dpages value, as well as running dbcc updateusage for the table.
Is it not possible to free up the space used by using this method ?
Is recreating and repopulating the table with only the required columns the correct / only way to do this ?

thanks,
manoj

View 1 Replies View Related

DB Design :: Alter View To Add Columns

Jun 9, 2015

I would like to alter view to add columns. My base table is populated by application and the base table name is always change. Is there any way to add some extra columns to this view.

One way is to create a sp to add columns and use the view inside the sp. But I would like to know is there any way to alter the view. 

View 4 Replies View Related

SQL Server 2012 :: View Results Not Displaying Nil Columns

Sep 5, 2014

I have a view I've created which displays client sortname, partner and date added which displays 7 results.

When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.

Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?

Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN

[Code] ....

In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...

View 8 Replies View Related

Cannot Alter Or Drop Fields

May 15, 2006

I must change replication schema from:
Publisher - SQL2000(8.0.2039), Disrtibuter/Subscriber - SQL2000
to:
Publisher - SQL2000(8.0.2039)(The same server), Disrtibuter/Subscriber - SQL2005(9.0.2047)(new server)

I have removed all subscription, publications, distributer and publisher. So there should not be now any information about replication.

Now I want to make some changes with tables and i try to alter and drop columns on publisher database. But it wont do... I always receive messages that tables and fields are being used in replication:

ALTER TABLE Clients DROP COLUMN ABCCode
Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'ABCCode' is currently replicated.

I tried to use sp_MSunmarkreplinfo and sp_removedbreplication but it did not help. I also tried to drop column with sp_repldropcolumn but got this message: Invalid object name 'sysmergearticles'.
Looks like server (publisher) still has information about old replication. But how to delete this information? And how can I alter and drop fields?

Thanks

View 4 Replies View Related

Why Variables In A Alter Or Drop Does Not Work.

Oct 26, 1999

I am writing a procedure to drop a specific user's schema only. I select all his/her table name and foreign key in a temp table first and then use a cursor to get the table name and foreign key, and begin drop foreign keys and drop table. However, sql think it is wrong with the syntax like "alter @table_name". Does anyone have a clue, what is wrong with my syntax. Or someone know how to drop one specific user's schema only (Other user may have same table in the database, and you don't want to drop that.

Attached is my scripts: please correct it. Thanks.

CREATE procedure sp_drop_schema @user sysname
as

select o.name table_names, o.id table_id, s.parent_obj Key_id, s.name key_name, u.name users_name
into #temp1
from sysobjects o inner
join sysobjects s on o.id=s.parent_obj
join sysusers u on o.uid=u.uid
where s.type='f' and u.name=@user

declare cur_f cursor
for select table_names, key_name

declare @table_name varchar (80), @key_name varchar (80)

open cur_f

fetch next from cur_f into @table_name, @key_name
while @@fetch_status=0 begin
alter table @table_name
drop constraint @key_name
fetch next from cur_f into @table_name, @key_name
end
close cur_f
deallocate cur_f
drop table #temp1

select o.name table_name, o.id table_id, o.uid users_id, u.name users_name
into #temp2
from sysobjects o
inner join sysusers u on o.uid=u.uid
where type='u' and u.name=@user

declare cur_temp cursor
for
select table_name
from #temp2

declare @tab_name varchar (80)
open cur_temp
fetch next from cur_temp into @tab_name
while @@fetch_status=0 begin
drop table @tab_name
fetch next from cur_temp into @tab_name
end
close cur_temp
deallocate cur_temp
drop table #temp2

View 1 Replies View Related

Thoughts On ALTER Vs. DROP & CREATE.

Nov 8, 2006

Thought I should post in the newbie forum for a while, instead. :-)

I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)

/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */

While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?

Your thoughts, oh SQL gurus?

View 12 Replies View Related

When Do I Need To Drop Column Statistics When Doing An Alter?

Aug 30, 2007



I need to know definitively when I need to drop and recreate column statistics (histogram) when altering a column. Empirically, it seems I can lengthen a varchar or change a not null column to a nullable one, and then existing statistics dont' cause


Msg 5074, Level 16, State 1, Line 1

The statistics 'c1' is dependent on column 'c1'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN c1 failed because one or more objects access this column.


But, if I change a column to not nullable or shorten a column, I get the error.

Is this the complete description of when I need to drop and recreate column statistics and when I don't?

View 3 Replies View Related

Alter Column Drop Identity

Apr 18, 2008

Is there any way to remove the IDENTITY property of a column? I originally used it, but it caused so many headaches with ADO.NET that I've decided to just increment the darn things manually. So I want to turn it into just a normal integer column.

If there's no direct way, can someone suggest a quick way to create a new column, copy all the old values into the new column, change the primary key to the new column, and drop the old column?

Thanks.

View 3 Replies View Related

Alter Table Drop Column Error

Apr 11, 2001

1. We have user defined type (Sql type = bit ) with rule setting it to 0
2. New column(isVIP) was created using this user defined type
3. sp_unbindrule was used to unbind rule from new column (isVIP)
4. Alter table Client drop Column isVIP !!!! (invalid syntax )

Any ideas ?
Thanks

View 4 Replies View Related

How To Alter Table Drop Constraint @variable?

Jun 12, 2003

I need to run the alter table to drop a default. However, the default name is kind of 'dynamic' from around 1000 databases, thus I need to run the following sql to get the name to a variable. Now, it looks the alter table statement does not like to drop a vaiable, is there a solution about it?

declare @radius_default varchar(40)
select @radius_default = (select sobj.name from sysobjects sobj
inner join syscolumns scolumn
on sobj.ID = scolumn.cdefault
where scolumn.name = 'radius' and sobj.name like '%LandMarks%')
print 'Need To Drop @radius_default: ' + @radius_default
--====================================
alter table LandMarks drop constraint @radius_default

thanks
David

View 1 Replies View Related

Is There A Alter Table Xxxx Drop Default Yyy ?

May 11, 2005

Hi:
I need to change a column's datatype from tinyint to int as follows:
alter table tableName
alter column column1 int

but with error <<'DF__LandMarks__color__6A50C1DA' is depending on it.>>

However, this old default is not part of the constraint. Thus, the only way is to delete it from sysobjects.

unfortunately, the following code I have to commented since they could only be executed line by line, not within a begin...end block.
--exec master.dbo.sp_configure 'allow updates', 1
--reconfigure with override
--delete from sysobjects
-- where name = 'DF__LandMarks__color__6A50C1DA'
-- and type = 'D'
--exec master.dbo.sp_configure 'allow updates', 0
--reconfigure with override

I need to update 10 server around 2500 databases with this change.
I have looked INFORMATION_SCHEMA related views, but not found default related, maybe I missed something.

thanks
David

View 4 Replies View Related

Drop Create Vs Alter In Regards To Stored Procedures

May 17, 2007

Anyone have any compelling arguements for using one over the other?



Thanks



Mercy

View 7 Replies View Related

SQL Security :: Deny Alter And Drop Permissions On ONLY Stored Procedures

Aug 19, 2015

We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).

View 17 Replies View Related

Alter View / Create View

Aug 14, 2000

I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.

I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.

If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.

View 1 Replies View Related

Alter More Than One View

Jan 4, 2007

Hi All,I am new to this group and this is my first doubt i am facing atpresent.I am doing data migration. In this sequence i need to alter few views.Alter in the sense, inside the existing query of view i want to includeone more column.I want to do it inside one single script. If i run the script all viewsshould get updated.Any help on this will be greatful.my mail id is Join Bytes!.Thanks in advance

View 9 Replies View Related

View Permissions Alter

Aug 10, 2000

I have granted a developer the alter view permissions on some views in our production server which now allow him to open the view for modification. When he tries to save his changes he gets an error that he doesn't have create view permission. If seen this behavior before when you modify a table, does SQL Server 7.0 actually drop and recreate the object? If so, would he then need create permissions on views also?

View 2 Replies View Related

Alter View And Concatenate In One Row (was: Is It Possible?!)

Aug 28, 2006

Hey Folks,

I have a View that I'm using to power a Crystal Report. I want to include an extra column that will be a string containing all the values from a single column of another table, seperated by comma.

So for example:

Project | Staff
----------------------
TestProject1 | "Jonathan, Greg, Joe"

Is this possible? Where do I do it? From the SQL Server end or in the report?

View 2 Replies View Related

Write A CREATE VIEW Statement That Defines A View Named Invoice Basic That Returns Three Columns

Jul 24, 2012

Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.

This is what I have so far,

CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID

[code]...

View 2 Replies View Related

Table And View Will Not Drop

Jun 13, 2001

We have a table created by an application, and a view that joins the table with other tables.

For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.

However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.

Anyone know what could be the problem here?

View 2 Replies View Related

Drop Or Delete View

Jul 12, 2007

hello,



im creating a sp that creates a view from a query then bcp to csv file.



my problem is that when i start the sp, it complains the sp already exists....yes it does, however, i don't if im going about this the wrong way...but i tried the following to no avail



DROP IF EXISTS v_participantTrades



now, does that syntax not work for views?



View 4 Replies View Related

Can I Add Alter View In The Stored Procedure?

May 13, 2008

Hi,
I am using SQL 2000( Version: 8.00.000)
I want to ask can I add View in the stored procedure like the following:


Create Procedure sp_createTblLogReasonColorView
@m int,
@y int
AS
BEGIN

ALTER View vw_tblLogReasonColor
AS
Select * From tblLogReasonColor where Year(n_Delivery_Date) = @y AND Month(n_Delivery_Date) = @m

END

Thanks for you reply.

View 1 Replies View Related

Alter View Fails To Replicate

Oct 18, 2007

prerequisites:
MSSQL 2005 64bit as publisher (transactional replication)
MSSQL 2000 32 bit as subscriber (pull, read-only)

All objects are set to replicate except foreign keys and defaults.
There is a linked server DW on both the publisher and the subscriber that both can access (sa and user logins mapped)
The snapshot generation and loading is fine.

but then I issue this statement at the publisher:

ALTER VIEW [dbo].[v_Test] as
select * from DW.DW_DB.dbo.v_Test

but it fails to replicate at the subscriber with the message:
Category:COMMANDSource: Failed CommandNumber: Message: ALTER VIEW [dbo].[v_Test] asselect * from DW.DW_DB.dbo.v_TestCategoryQLSERVERSource: SubscriberNumber: 7399Message: OLE DB provider 'SQLOLEDB' reported an error.

I tried the command on the Subcriber via Query Analyzer and it works.
What gives?

All suggestions are welcome!

View 2 Replies View Related

Alter Columns?

Apr 1, 2004

Hello,

I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.

I have succesfully edited a single column with the following code:
USE THCIC
ALTER TABLE PudfTest
ALTER COLUMN
DISCHARGE VARCHAR(6) NULL

However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.

Any help would be greatly appreciated.
Thanks!

View 1 Replies View Related

If Exists DROP PROCEDURE/VIEW

May 20, 2007

how can I drop a propcedure or a view without error for MS SQL 2000/2500

for a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]


and for a procedure

IF EXISTS ???
DROP PROCEDURE [dbo].[SP_myTABLE_Count]

thank you for helping

View 4 Replies View Related

Can We Recompile View Instead Of Drop And Create

Jan 31, 2008

Hi,
I increased one of my base tables column which is referenced in view

I noticed sql server didn't recognized this change and its still showing old field size in the view.

I can simply drop and create it again. But wanted to know if there is any way (command/sp) to recompile the view which will be easy to deploy in production as patch.

Thanks
- D

View 11 Replies View Related

OLE DB Destination Table/View Drop Down

Jan 26, 2007

Is there any way around (or will there be) using the drop-down? It takes several minutes when running against an Oracle Apps database to populate that dropdown with the several hundreds of tables and views.

TIA.

View 4 Replies View Related

ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.

Mar 7, 2008

Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
 

View 1 Replies View Related

Matching A View's Columns To It's Underlying Table's Columns

Jul 20, 2005

Hello,Using SQL Server 2000, I'm trying to put together a query that willtell me the following information about a view:The View NameThe names of the View's columnsThe names of the source tables used in the viewThe names of the columns that are used from the source tablesBorrowing code from the VIEW_COLUMN_USAGE view, I've got the codebelow, which gives me the View Name, Source Table Name, and SourceColumn Name. And I can easily enough get the View columns from thesyscolumns table. The problem is that I haven't figured out how tolink a source column name to a view column name. Any help would beappreciated.Garyselectv_obj.name as ViewName,t_obj.name as SourceTable,t_col.name as SourceColumnfromsysobjects t_obj,sysobjects v_obj,sysdepends dep,syscolumns t_colwherev_obj.xtype = 'V'and dep.id = v_obj.idand dep.depid = t_obj.idand t_obj.id = t_col.idand dep.depnumber = t_col.colidorder byv_obj.name,t_obj.name,t_col.name

View 2 Replies View Related

Alter Multiple Columns In SQL

Mar 20, 2008

This does not work, incorrect syntax... Please help: ALTER TABLE dbo.classifieds_HotelAds ALTER COLUMN (
HasValetParking varchar(1) NULL,
HasContinentalBreakfast varchar(1) NULL,
HasInRoomMovies varchar(1) NULL,
HasSauna varchar(1) NULL,
HasWhirlpool varchar(1) NULL,
HasVoiceMail varchar(1) NULL,
Has24HourSecurity varchar(1) NULL,
HasParkingGarage varchar(1) NULL,
HasElectronicRoomKeys varchar(1) NULL,
HasCoffeeTeaMaker varchar(1) NULL,
HasSafe varchar(1) NULL,
HasVideoCheckOut varchar(1) NULL,
HasRestrictedAccess varchar(1) NULL,
HasInteriorRoomEntrance varchar(1) NULL,
HasExteriorRoomEntrance varchar(1) NULL,
HasCombination varchar(1) NULL,
HasFitnessFacility varchar(1) NULL,
HasGameRoom varchar(1) NULL,
HasTennisCourt varchar(1) NULL,
HasGolfCourse varchar(1) NULL,
HasInHouseDining varchar(1) NULL,
HasInHouseBar varchar(1) NULL,
HasHandicapAccessible varchar(1) NULL,
HasChildrenAllowed varchar(1) NULL,
HasPetsAllowed varchar(1) NULL,
HasTVInRoom varchar(1) NULL,
HasDataPorts varchar(1) NULL,
HasMeetingRooms varchar(1) NULL,
HasBusinessCenter varchar(1) NULL,
HasDryCleaning varchar(1) NULL,
HasIndoorPool varchar(1) NULL,
HasOutdoorPool varchar(1) NULL,
HasNonSmokingRooms varchar(1) NULL,
HasAirportTransportation varchar(1) NULL,
HasAirconditioning varchar(1) NULL,
HasClothingAiron varchar(1) NULL,
HasWakeupService varchar(1) NULL,
HasMiniBarInRoom varchar(1) NULL,
HasRoomService varchar(1) NULL,
HasHairDryer varchar(1) NULL,
HasCarRentDesk varchar(1) NULL,
HasFamilyRooms varchar(1) NULL,
HasKitchen varchar(1) NULL,
HasMap varchar(1) NULL,
HasWiFi varchar(1) NULL,
GDSChainCode nvarvarchar(1) NULL,
GDSChainCodeName nvarchar(50) NULL,
DestinationID nvarchar(50) NULL,
DrivingDirections nvarchar(MAX) NULL,
NearbyAttractions nvarchar(MAX) NULL,
IANHotelID int NULL,HasClothingIron bit NULL
)
GO
COMMIT

View 8 Replies View Related

Alter View Hangs - Merge Replication SQL 2005

Dec 18, 2006

Background - I have a publication that propigates schema changes. I have a view in which I want to remove a column.

Error - Going by what the BOL says, I use Alter View and delete the column from my select statement. I issue the alter view command against the Publication database and it just "churns". I do not get any locking errors or any other type of error, but the statement never completes execution. I watched it run for 10 minutes and cancelled the query. Executing the same statement against a copy of the database that is not being published executes in 1, 2 seconds.

Here is what I am doing:

Old View: Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc,

table2.txt_sort_order

FROM table1 join table2 on table1.status_code = table2.status_code

The query I am executing:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW myview

AS

Select table1.record_number, table1.record_date, table1.status_code, table2.status_desc

FROM table1 join table2 on table1.status_code = table2.status_code

View 1 Replies View Related

Alter Table Columns Automatically

Feb 19, 2015

I need to perform this task. say I have a table that the columns are dates.

Table months:
[02-2015],[03-2015], [04-2015],[04-2015]....[02-2016].

When the new month comes I want the table to alter the columns automatically instead of manual how we do it now. The new Month table should delete [02-2015] and replace it with [03-2015].

table months:
[03-2015],[04-2015],[05-2015],[06-2015]....[03-2016].

View 3 Replies View Related

How To Alter Multiple Columns At Time

Dec 4, 2006

Hi all

ALTER TABLE <Table_Name> ALTER COLUMN <column_name> <Data_type>

above query alters the sigle column but how to alter multiple columns at time

saivyshnav
Junior DBA

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved