Drop All Column Extended Propterties

Mar 25, 2006

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?



View 1 Replies


ADVERTISEMENT

Delete / Drop Extended Stored Procedure In 2005

Oct 5, 2007

We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.

Do I just have to control the permissions of these procedures tightly?

Thanks In Advance
MPM

View 1 Replies View Related

Drop Extended Property MS_Description Of ALL Tables And ALL Columns

Sep 11, 2007

Hi,

Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?

Regards,
Alejandroo

View 6 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

How To Add Table Column Extended Properties To A View.

Apr 7, 2008

I am using SqlServer 2005 and I have created small sample table with culumns that have Extended properties.
FirstName have [First Name] extended propery called M_Caption.
Then I created a view from the table to use in an access database.
My problem is, I can't get the table column extended properties to show up in the view column extended properties.

Here is the table

/****** Object: Table [dbo].[Person] Script Date: 04/07/2008 14:42:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[Id] [int] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'FirstName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'Last Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'LastName'



I created this view

/****** Object: View [dbo].[View_1] Script Date: 04/07/2008 14:50:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT FirstName, LastName
FROM dbo.Person
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "Person"
Begin Extent =
Top = 6
Left = 38
Bottom = 99
Right = 189
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'



Thanks

View 1 Replies View Related

Table Column Extended Field Properties

Jul 17, 2007

through MS SQL Server Management Studio I have a database defined as:



dbname

- Tables

- dbo.xyz

-Columns

Field definition_a

Field definition_b

Field definition_c

...



For each "Field definition" I can define "extended properties"





So the question is, from a stored procedure in C#, how can I read through the "Field definitions"

looking for an extended property of a specific integer value?



/Boyd

View 2 Replies View Related

DROP COLUMN

Feb 14, 2000

Does anyone know why this does not work?

ALTER TABLE deal DROP COLUMN TestField

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'TestField'.

Thanks,
Paul

View 4 Replies View Related

How To Drop A Column?

Aug 30, 2006

Hi:

'Unfortunately', there is still a SQL6.5 sp4 server that I need to handle.
I need to drop a coulmn xyz from an important table tblABC.
But neither Enterprise manager could do it nor
'alter table tblABC drop column xyz' could run.

Without the hastle of makin a tblABCcopy and copy all data from tblABC except column xyz and re-do all fks; is there another way to drop the column?

thanks
-D

View 1 Replies View Related

Drop A Column

Apr 6, 2015

I have a question, Every month I get a request from cutomer service to go into a SQL VIEW and DELETE a column to reflect the past Month. How to write a query to create a SQL job to performed this task automatically. For example today I deleted the column [2015/03] which represents the month of March. The columns left are [2015/04] representing the current month, [2015/05], [2015/06],[2015/07]....[2016/08].Next month I will have to delete column [2015/04]and the current month will be [2015/05]....etc.

So I'm thinking I will have to Drop the column and also Maybe using a date funtion to look at the current date and format it to reflect [YYYY/MM] and IF the Column Month is less than current date month then drop the column.

View 3 Replies View Related

Drop Column

Mar 15, 2007

How to drop single column in a table.........

View 3 Replies View Related

Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod

May 15, 2008

i have created the folowing function but keep geting an error.

Only functions and extended stored procedures can be executed from within a function.

Why am i getting this error!

Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)

set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'

execute sp_executesql @sqlval
return @ret
end

View 3 Replies View Related

How To Drop Primary Key By Specifying Column Only?

Apr 24, 2007

I checked column from xsd file which created from DataBase.
And before installing my application I will check any column in destination base whether have complete column or not.
 
And if there is any column which is not wanted column(not same as xsd structure)
I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ......"
and Execute it by program initialization.
 
So
I need delete it by run-time
 
 
However some column may be Primary Key with any reason
 
That's why I can't delete them by simple command
 
I expect to delete them by specific column instead of specific constraint name which is not sure name.
 
 
Please advise me....

View 2 Replies View Related

Drop A Column In A Table!!

Aug 2, 2001

Hi all,
how can I drop a column in a table.

View 1 Replies View Related

Can't Drop Column That Has A Default

Jun 13, 2008

I did this...

ALTER TABLE tm_spn_equip_def ADD Customer_No int NOT NULL DEFAULT dbo.f_spn_GetCustomerNo() WITH VALUES

Now when I attempt to do this...

ALTER TABLE tm_spn_equip_def DROP COLUMN Customer_No

I get an error:

Msg 5074, Level 16, State 1, Line 1
The object 'DF__tm_spn_eq__Custo__019E3B86' is dependent on column 'Customer_No'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN Customer_No failed because one or more objects access this column.


How can I get around this?

TIA!

View 4 Replies View Related

How Can I Drop A Column From A Table?

Dec 13, 2007

Hi all!

One more question: how can i drop a column from a table?

Greetings

landau

View 2 Replies View Related

Drop Column Problem

Jul 20, 2005

Hello,I'm just returning to MS SQL Server after two years of dealing withSybase ASE. I need to drop a column, using the alter table command.I keep getting an error indicating that a constraint is using thecolumn. Here is the create script for the tablecreate table mytable(col1 char(1) not null,col2 char(1) default 'A' not null)Here is the alter table command:alter table mytable drop column col2When I run it I get the following error:Server: Msg 5074, Level 16, State 1, Line 1The object 'DF__mytable__col2__114A936A' is dependent on column'col2'.Server: Msg 4922, Level 16, State 1, Line 1ALTER TABLE DROP COLUMN col2 failed because one or more objects accessthis column.Is there anyway to tell the database to drop all column constraintswhen the column is deleted?Thanks,James K.

View 2 Replies View Related

How Do I Drop A Column From A Table

Oct 6, 2007



I have a database that I have made using sql server 2005 express edition. I want to run a query to drop one of the columns from one of the tables. I have tried the following but had no luck.

USE database


DROP column FROM table



This is the error that I got when I tried this.


Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'FROM'.

Of course, my query had the real database, table and column names. I have generic ones that I have provided here.

Do I have to alter the table instead of dropping the column that I don't want?
Do I have to remake the table without the column that I don't want?

View 1 Replies View Related

Drop Column With Constraint

Sep 25, 2007



Hello !

I am using Microsoft SQL Server 2000

I am trying to drop a column that has a constraint, executing the script inside a transaction:

BEGIN TRANSACTION

ALTER TABLE MOPTeste DROP CONSTRAINT FK_IDMOPPais


ALTER TABLE MOPTeste DROP COLUMN IDMOPPais

COMMIT

If i dont commit the drop constraint, it wont let me drop the column

Solutions?



View 3 Replies View Related

Drop Indentity From Column

Dec 6, 2007

I am trying to remove the Indentity property from a column so I can do some clean up. Then will need to add it back. Adding Indentity property back is not a problem, but cant figure out how to remove it in the first place.

Any help would be greatly apprecaited.

Thanks
Dave

View 4 Replies View Related

Drop Identity Property Of A Column

May 24, 2005

Is there a way to remove the Identity property of a column in SQL Server 2000? 
The statement:
<code>
ALTER TABLE <table name> ALTER COLUMN <column name> DROP IDENTITY
</code>
returns a syntax error.
Thank you,
Jeff

View 11 Replies View Related

How To Drop UNIQUE CONSTRAINT On Only One Column

May 5, 2014

I am very new to sql and I have got this doubt.

CREATE TABLE employee
{ EMP-ID VARCHAR UNIQUE,
DESIG VARCHAR UNIQUE,
SALARY INT };

Probably this is worst table ever created :).Now How to drop UNIQUE constraint on only column lets DESIG.

if i write

ALTER TABLE employee
DROP CONSTRAINT myuniqueconstraint;

will drop constraints on both columns which I dont want.

Any query that remove UNIQUE CONSTRAINT only on DESIGN column.

and one more clarification,

ALTER TABLE employee
MODIFY SALARY INT NOT NULL;

ALTER TABLE employee
ALTER COLUMN SALARY INT NOT NULL;

which of the above two is right query to add NOT NULL constraint to the above employee TABLE?

View 1 Replies View Related

Reclain Space After DROP COLUMN

Jul 23, 2005

I have a huge table in it own tablespace (250 GB). I just deleted more thanhalf of the columns in that table, but I didn't see any change in allocatedspace. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also saysthat CLEANTABLE only reclaims space for columns with text, char datatypes.How can I get space back for integer, decimal, number datatypes? I'm justall out of ideas.

View 7 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

SQL Server 2012 :: Drop And Update Column?

Feb 16, 2015

I am looking for standard sql code for below 2 concern.

1- I want to drop the column Rowchecksum to all the table where table name start with ArchiveBbx

2-I want to update all the table where table name start with ArchiveBbx

example:-

Update table Archivebbxfbcc
set Rowchecksum=HASHBYTES('MD5', CAST(CHECKSUM(Col001, Col002, Col003, Col004) AS varchar(max)))

View 3 Replies View Related

Reporting Services :: How To Drop Unique Key Column

Jun 9, 2015

How to drop the unique key column in sql.

View 2 Replies View Related

Reporting Services :: How To Drop Primary Key Column

Jun 9, 2015

How to drop the primary key Column in Sql and How to drop Primary key Constraint From Column at  Table  in Sql.

View 6 Replies View Related

Transact SQL :: Check DB Consistency After DROP COLUMN

Apr 25, 2015

I have a SQL Server 2005 DB in which a design change requires me to drop a column.Is there a way of identifying all references in the DB including functions & stored procs's that reference this column? I understand there are 3rd party products that can do this but was wondering if there was something baked into SQL Server itself.

View 8 Replies View Related

Locking Column (cannot Drop Columns In The Table)

May 27, 2015

How can I pervert dropping the column in the table (probably some process doing that and I want to find it) ? I need to be able to modify but not alter / drop column .

View 4 Replies View Related

Urgent: How To Drop Identity Nature Of A Column Using T-sql(Sql Server 7).

Dec 19, 2000

I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn't able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.

View 5 Replies View Related

SQL 2012 :: Drop All The Auto Generated Column Statistics?

Dec 29, 2014

My question: Is it okay to drop all the auto generated column statistics? (for the following scenario)

- I am cleaning up unnecessary objects (tables, unused indexes, overlapping statistics etc) from databases and found out there are more than 1400 auto generated column statistics on one database (lets call it A).
- Database A was used to be our reporting database but from last several years we are using database B for reporting. DB A has all the historical data while DB B only has valid records.
- We are updating all the column statistics with full scan nightly on database A and it is talking almost 2.5 hours to do that. Now I want to drop all the "unnecessary" statistics those were created when DB A was reporting database and they are no longer in use. There is no way to know the creation date of the column statistics that I know of. Statistics "last update date" is of no use because of our nightly job. So I was thinking of dropping all the auto generated column statistics and let the sql server create as it needs from now.

View 0 Replies View Related

SQL Server 2008 :: How To Fetch Drop Down Values Stored In XML Column

Aug 13, 2015

Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.

<DropDown Prompt="Case Manager" Column="case_manager" AddOnly="false" ReadOnly="false" Required="false" CanHaveNotes="no" LabelCssClass="" IndentLevel="1" FirstEntryBlank="false" CssClass="" DefaultValue="" ChoiceType="1">
<Items>
<Item Id="1">ABC</Item>
<Item Id="2">DEF</Item>
<Item Id="3">GHI</Item>
<Item Id="4">JKL</Item>
<Item Id="5">MNO</Item>

[code]....

View 1 Replies View Related







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