Drop Extended Property MS_Description Of ALL Tables And ALL Columns
Sep 11, 2007Hi,
Is there an easy way (a sql script) to drop the "MS_Description" of all tables and all columns in my database?
Regards,
Alejandroo
ADVERTISEMENT
Is There A Way To Drop All The Columns Called Some_name In All The Tables At The Same Time
May 15, 2008Hi,
In my DB, many of my tables have a column named upsize_ts I have already been told that it is not linked in any way with the data in the DB. The data type of these columns is timestamp and every record in those colum is this : "<Binary>"
Now maybe it's because the whole DB comes from an export from an Access DB, or maybe not. I just had a few questions:
1/ Does anyone know why that column got generated and what it means? (optional)
2/Now the real question: is there any statement allowing me to "mass-drop" all those columns named "upsize_ts" in every table of my DB at the same time? Or at least any way not to do it one drop table at a time?
Thanks.
Regarding Extended Property
Oct 31, 2006hi,
Can anyone tell me how to use the extended stored property like sp_addextendedproperty in java code?
Thanks,
Meghna
How Do I Read An Extended Property Of A Database In Code?
May 8, 2006I have recently had the need to create a utility program to upgrade the schema of an existing database for users of my program at install time. I will be using SQL Management Studio to generate the change scripts the program will use, but I will need some kind of way to know what version of the database a user has installed.
After poking around in SQL Management Studio I found a place for "Extended Properties" on a database, and I thought this would be the perfect place to add a property called "SCHEMA_VERSION" to know what version of the database is installed.
I have no idea how to read and update this value in code though. Can someone provide a small example on how to read and update an extended property on a database? Any .NET language example is welcomed; C# is preferred. Thanks in advance!
Drop All Column Extended Propterties
Mar 25, 2006Need 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?
Delete / Drop Extended Stored Procedure In 2005
Oct 5, 2007We 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
Drop Identity Property Of A Column
May 24, 2005Is 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
Drop All Indexes In A Table, How To Drop All For User Tables In Database
Oct 9, 2006Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedRollback Will Drop Created Tables And Drop Created Tables In Transaction..?
Dec 28, 1999Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
Truncated MS_Description (only Displays First 256 Characters)
Apr 29, 2008SQL Server 2000 documentation says that the syproperties value (via sp_addextendedproperty) has a limit of 7,500 bytes. So I have a property (MS_Description) on a column which is 654 characters long. When I run fn_listextendedproperty or if I select value from sysproperties I get only the first 256 characters returned. As if the field was truncated when I added it. BUT, if I select something like right(cast(value as varchar(1000)), 200) from sysproperties, I see the remaining characters. So they are there but I can never return the entire string at one time.
Any ideas why this is and how I can get around it?
Find All Columns That Have Identity Property
Mar 19, 2008
How do i Find all Columns that have Identity Property in a database. That is i will like to know all columns in a database that are identity columns.
I am using SQL Server 2005. Thanks
How To Drop An Identity Column From All Tables Tables In A Database
Mar 2, 2008Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedUsing Alter Table To Drop Columns
Nov 5, 2000I 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
Drop Null Columns From A Table
Jan 11, 2005Hello,
Is then any type of statement I could run to drop all the columns of a table that have a NULL value thoughout or must this be done manually?
Thanks,
Bryan
SQL Server 2008 :: Add Target Specific Extended Properties To Replication Subscriber Tables
Oct 6, 2015I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
View 0 Replies View RelatedSQL 2012 :: How To Drop Columns From Alter View
Mar 20, 2015I 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.
Reclaim Disk Space After DROP Columns
Aug 2, 2006Hello,I tried to make what one reply advice to recover the disk space afterDROPed columns but it doesn't work. I did DBCC DBREINDEX to the tablebut nothing changes.Any other solution?Ignacio
View 5 Replies View RelatedLocking Column (cannot Drop Columns In The Table)
May 27, 2015How 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 RelatedReplicating Tables With IDENTITY Property
Oct 24, 2000Hi,
Please let me know if it is possible to replicate a table with identity property defined in it. Both the publisher and subscriber tables have identity property defined. Which option should be used while setting up transactional replication to allow the identity values at the publisher pushed to the subscriber, which also has identity property defined? Not for replication option with the identity property also fails. Whichever option I choose I get the error, 'An explicit value for the identity column in table 'jobs_id_nfr' can only be specified when a column list is used and IDENTITY_INSERT is ON.' This works only if the identity property is not defined at the subscriber. But, I need to have the identity property defined at the subscriber also because the subscriber should be an exact copy of production.
Thanks in advance,
Praveena
Drop Tables...
Jan 31, 2008Hi,
Is thr any query to drop around 20 tables at a time????
or shud i need to drop them individually one at a time???
Drop All Tables In Database
Apr 25, 2007what is the sql query to drop all tables in a database in sql server 2000
View 5 Replies View RelatedDrop Multiple Tables
Mar 7, 1999Hi,
I want to write a script that will drop all tables in a database that begin with BACKUP.
Is there an easy way of doing this?
Thanks
Phil
How To Drop All PKs On Tables In Database?
Feb 1, 2007I have a list of 35 tables that need to drop the primary key index from in my database.
My problem is as follows for these 35 tables:
1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?
I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.
Thanks
Drop Multiple Tables
May 30, 2008I need to drop multiple tables in an SP... all the tables starts with a string that I can use... please suggest me the best way to drop all these tables in an SP that has more than just this.. Thanks!
View 2 Replies View RelatedFile To Drop Tables
Dec 6, 2006I need to create a file that removes (drops) all of your database objects.
View 12 Replies View RelatedLinkage From 2 Drop Down Tables?
Jun 15, 2007Hello all, im using visual web developer btw. Im using the excellent tutorial here at http://www.asp.net/learn/videos/view.aspx?tabid=63&id=49 Works a treat. For my catalogue/database. i have 2 tables using the drop down menu - one is a "Buyers guide" (a list of the product) and an "application list" (this one is a list of the motorbike).
So in essence they are the same tables, but of course moved around with slightly different ways. What i would like to, is to some how make a refferenced link to each - Once one of the drop down menus has been linked to a product, theres a column that tells u what bike is being used....i therefore want to have a link so the seconmdary drop down menu - but i do not know how i can do this. Any ideas guys/gals? thx.
SQL Tries To Drop Tables That Don't Exist
Mar 14, 2008When I am initializing a transactional replication from SQL 2005 to Oracle 10g, SQL tries to drop tables that don't exist. The properties option for the articles specifically states "if the name is in use:". The name is not in use, yet SQL still tries to drop non-existent tables, which causes the replication to halt. Anyone seen this before, or have any ideas what to do about it?
Thanks!
How To Find Out All The Statistics From All The Tables And Drop Them
Jul 21, 2003How to find out all the statistics from all the tables and drop them..any script anyone can help with?
When we are trying to make datatype changes in few related tables,it's giving error saying that some statistics are dependent onthe column blah blah...
Thanks,
Sheila.
Tables Exist But Wont Drop
Dec 11, 1998I have two tables that will not drop in a Database. I'm running SQL 6.5 on a Compaq Proliant.
Symptoms: Both tables show up in sysobjects, sysindexes, and syscolumns. Both allow SELECT, sp_rename, and truncate, but just hang ISQL or the Enterprise Manager when I try to drop them. I ran a DBCC CheckTable on them with no errors reported. Any ideas??
Drop Index On System Tables
Jul 20, 2005SQL SERVER 2000System let's you alter the system tables and add indexes. However, it won'tlet you drop the index afterward.Anybody know how to drop an index on a system table?Thanks,Kevin
View 4 Replies View RelatedDrop All Tables In Db That Have Specific Name Convention
Oct 23, 2007I have 1000's of tables. Some are of the form dbo.VT_2006-10-12. I'd like to drop all tables with the "VT" in the table name. How is the best done?
View 6 Replies View RelatedTask To Create And Drop Tables
Apr 12, 2006I have a package that i want to move between enviroments. Therefor i need to create a package that creates all my tables on the new server.
like
-- "if exists (select * from dbo.sysobjects where id = object_id(N'[table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table]
GO"
The case is that i have around 30 tables, and my question is therefore
I know that you can create the drop and create script for one table at the time in the SQL Server Management Studio, but is there an easier way to do this
Looping To Drop Temp Tables
Sep 10, 2007Hello,
I have a query that's in development that uses several temp tables. In order to test the query repeatedly while it's being written I have the following code at the beginning to discard the temp tables. This allows the query can recreate the temp tables when called in the code.
if object_id('tempdb..#temp1') is not null drop table #temp1
if object_id('tempdb..#temp2') is not null drop table #temp2
if object_id('tempdb..#temp3') is not null drop table #temp3
if object_id('tempdb..#temp4') is not null drop table #temp4
if object_id('tempdb..#temp5') is not null drop table #temp5
Even though this works, it takes multiple lines of code. One of my queries has to drop 12 temp tables, thus 12 lines of code. I have been experimenting with looping the above as follows:
declare @n as nvarchar(3), @table as nvarchar(10)
set @n = 1
while @n <= 5 begin
set @table = '#temp'+@n
if object_id('tempdb..#temp'+@n) is not null drop table @table
set @n = @n + 1
end
Unfortunately, the above does not work. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@table'.
I have also tried:
declare @n as nvarchar(3), @dropstmt as nvarchar(25)
set @n = 1
while @n <= 5 begin
set @dropstmt = 'drop table #temp'+@n
if object_id('tempdb..#temp'+@n) is not null @dropstmt
set @n = @n + 1
end
This does not work either. It gives this error message:
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@dropstmt'.
Does anyone know how to get this to work?
Thanks.