How to detect an EndPoint is existed or not?

How to detect an EndPoint is existed or not?
I would like to detect it before drop an endpoint.
--FrankYou can check sys.endpoints to see if it exists.
IF EXISTS ( SELECT * FROM sys.endpoints WHERE name = 'my_endpoint' )
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> glsD:e%23YJwf4QGHA.3916@.TK2MSFTNGP11.phx
.gbl...
> You can check sys.endpoints to see if it exists.
> IF EXISTS ( SELECT * FROM sys.endpoints WHERE name = 'my_endpoint' )
>
I got it.
Thanks

How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

I tried using DROP Tables, Truncate Database, Delete and many more but it is not working. I want to delete all tables using Query Analyzer, i.e. through SQL Query.

Please help me out in this concern.

Nishith Shah

hi Nishith Shah

try this

EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?"

this is a hidden SP in sql server, this will be executed for each table in the database you connected (you cant rollback this)

if u want to delete it from the command prompt try this

EXEC xp_cmdshell 'SQLCMD -U <user> -P <password> -Q 'EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?" ' ,no_output

Best of luck.

Gurpreet S. Gill

|||Hi Gurpreet,
it worked man.......... thanx a lot for your reply!

Nishith Shah|||

Thanks man

|||Hi Gurpreet! once again.

you have shown me the perfect way to delete/drop all table using single SQL statement.

tell me if i just want to truncate/delete all the tables then how can i?

pls reply

Nishith|||

Hay man what you are asking for,if you just check my reply, the answere is there

ok, try this, this will delete/truncate all the Data from each table for in the database you connected

EXEC sp_MSforeachtable @.command1 = "DELETE FROM ?"

EXEC sp_MSforeachtable @.command1 = "TRUNCATE TABLE ?"

I too explain it now, as sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @.command1 is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where '?' is the name of the table.

try this, it will clear your comcepts

EXEC sp_MSforeachtable @.command1 = "SELECT * FROM ?" -- Selects all the rows form all the table

EXEC sp_MSforeachtable @.command1 = "PRINT '?'" --Just print the tables names with owner(dbo)

For more understanding, go for the MSDN or google, this is the right way.

If still you are confused do call me any time(I am an Indian, 24x7) at +91-99495-60051

Regards,

Thanks.

Gurpreet S. Gill

|||Hello Gurpreet,

thanks a lot for helping man and giving your cell # also. It worked again...
So, where r u working? as a?

do contact me anyhow on me.poison@.gmail.com or nishith82@.hotmail.com
atleast send me a blank email, i will understand its u.

thanks,
Nishith|||

You won't be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

-- First disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- Now enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

Thanks Denis, ya these things need to consider, before applying delete/truncate command.

Regards,

Thanks.

Gurpreet S. Gill

|||

HI people

I want to do this in MS Access database . Delete all tables . Is there a hidden SP here also ? or some other way . .

Plz help

|||

hi

i cant say anything about this, better to go for the MS-Access forum.

or

if you know the visual basic you can write the macro for that.

just check this link

http://www.codecomments.com/message725983.html

Regards,

thanks.

Gurpreet S. Gill

How to delete or drop a cursor?

Could anybody please tell me how to detete or drop a cursor?

Thanks,

-RL

A cursor is automatically dropped when the connection terminates.

But, if you want to do it manaully, use "CLOSE cursorname" and then "DEALLOCATE cursorname". See Books OnLine under @.@.FETCH_STATUS has a full example.|||Thanks a lot Tom.|||

Also, be sure to declare the cursor as LOCAL, or use a cursor variable

declare @.cursor cursor
set @.cursor = cursor for...

These are much safer to use, and will deallocate themselves if you don't.

|||Thanks a lot Louis.

How to Delete Multiple Stored Procedures ?

I would Like to delete all stored procedures from the database using sql statement, wild cards with DROP PROCEDURE , dont work
ThanksOriginally posted by pkrol
I would Like to delete all stored procedures from the database using sql statement, wild cards with DROP PROCEDURE , dont work
Thanks
Use PL/SQL:

BEGIN
FOR r in (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE' )
LOOP
EXECUTE IMMEDIATE 'DROP PROCEDURE '||r.object_name;
END LOOP;
END;
/

How to delete full text catalog in SQL server 2005

I copied a database. Then I need to delete full text catalog and
re-create one with exact same name. But it doesn't let me drop it. The
machine just hangs there forever when I try to drop it. So I read
through the usenet and found a way to delete it from
sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
apply ad hoc updates. So what can I do to delete this full text
catalog. I have also searched through all the sys.sp*, but don't see
one to delete sysfulltextcatalogs. Tried the one
sys.sp_full_text_catalog, but doesn't work.
Anyone knows a solution?
run profiler and see which command it is hanging on. Then you might want to
run it through query analyzer.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shao" <shellyshao@.gmail.com> wrote in message
news:1142468617.986356.208590@.i39g2000cwa.googlegr oups.com...
>I copied a database. Then I need to delete full text catalog and
> re-create one with exact same name. But it doesn't let me drop it. The
> machine just hangs there forever when I try to drop it. So I read
> through the usenet and found a way to delete it from
> sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
> apply ad hoc updates. So what can I do to delete this full text
> catalog. I have also searched through all the sys.sp*, but don't see
> one to delete sysfulltextcatalogs. Tried the one
> sys.sp_full_text_catalog, but doesn't work.
> Anyone knows a solution?
>
|||Did you try to remove the FT at service and table level? You can use the
stored procedures:
sp_fulltext_table
sp_fulltext_service
Antonio Soto
Solid Quality Learning
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"shao" <shellyshao@.gmail.com> escribi en el mensaje
news:1142468617.986356.208590@.i39g2000cwa.googlegr oups.com...
>I copied a database. Then I need to delete full text catalog and
> re-create one with exact same name. But it doesn't let me drop it. The
> machine just hangs there forever when I try to drop it. So I read
> through the usenet and found a way to delete it from
> sysfulltextcatalogs in sql 2000. But in sql 2005, it doesn't let me
> apply ad hoc updates. So what can I do to delete this full text
> catalog. I have also searched through all the sys.sp*, but don't see
> one to delete sysfulltextcatalogs. Tried the one
> sys.sp_full_text_catalog, but doesn't work.
> Anyone knows a solution?
>

how to delete distributionDB

after i disable replication. the distributionDB is still there.
where i try to delete it . a error message occured as below:
Error 3724: cannot drop the database because it is being used for replication
please help
Cheers
nick
back up your master database. Issue the following query:
UPDATE master..sysdatabases SET category = 0 where name='distribution'
delete the distribution database.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:E112AE00-DABA-4FB1-BE70-0CAA0791D62F@.microsoft.com...
> after i disable replication. the distributionDB is still there.
> where i try to delete it . a error message occured as below:
> Error 3724: cannot drop the database because it is being used for
replication
> please help
> Cheers
> nick
>
|||when i tried to run
UPDATE master..sysdatabases SET category = 0 where name='nickDistribtuion'
i got error as below:
Ad hoc updates to system catalogs are not enabled. The system administrator
must reconfigure SQL Server to allow this.
"Hilary Cotter" wrote:

> back up your master database. Issue the following query:
> UPDATE master..sysdatabases SET category = 0 where name='distribution'
> delete the distribution database.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <Nick@.discussions.microsoft.com> wrote in message
> news:E112AE00-DABA-4FB1-BE70-0CAA0791D62F@.microsoft.com...
> replication
>
>
|||sp_configure 'allow updates', 1
reconfigure with override
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:BB79FB0A-8BED-41D9-BE60-E539DCCFD9D7@.microsoft.com...
> when i tried to run
> UPDATE master..sysdatabases SET category = 0 where
name='nickDistribtuion'
> i got error as below:
> Ad hoc updates to system catalogs are not enabled. The system
administrator[vbcol=seagreen]
> must reconfigure SQL Server to allow this.
> "Hilary Cotter" wrote:
|||it works. thanks a lot.
Nick
"Hilary Cotter" wrote:

> sp_configure 'allow updates', 1
> reconfigure with override
> go
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Nick" <Nick@.discussions.microsoft.com> wrote in message
> news:BB79FB0A-8BED-41D9-BE60-E539DCCFD9D7@.microsoft.com...
> name='nickDistribtuion'
> administrator
>
>

How to delete a counter in a field ?

I want to delete a counter using an SQL query (alter, drop...)
What could be the SQL queryI want to delete a counter using an SQL query (alter, drop...)

What could be the SQL query

Are your trying to reset an identity column or to drop it?
Plz specify that clearly...

To reset an identity column use

DBCC CHECKIDENT ('table_name',RESEED,0)|||I want to delete the counter (identity) but keep the column.|||Unfortunately, you can't reset the IDENTITY property in Microsoft SQL because of the way that it is implemented. The only safe way to do this is:ALTER TABLE myTable
ADD COLUMN new_column INT NOT NULL -- set type and NULL-ability to taste
GO
UPDATE myTable
SET new_column = old_column
GO
ALTER TABLE myTable
DROP COLUMN old_column
GO
EXECUTE sp_rename 'mytable.new_colum', 'mytable.old_column'
GO-PatP

How to delete (drop) a database with OSQL

Hi,
I imported DBF files into a new SQL server database and I've been
developing a VB.Net application.
Now I would like to delete the current database and re-import a more
current set of DBF files into SQL.
Only problem, I can't seem to find a way to delete the database from
MSDE. I tried the OSQL 'drop database <name>' but it complains the
'database is in currently in use'.
I must be missing something simple.
Thanks
Richard
Hi ,
You might be in the same database while you are trying to execute the
command.
connect to the server and change the context to the master database and
also make sure that you dint have any sessions currently accessing the
database either remotely or from the same machine. This time the command
should go ahead fine
girish sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||It is possible that there are still connections to the database. If you are
on MSDE 2000 the following is a sure way to drop a database:
-- Kick everyone out of the database
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Go somewhere else yourself as well
USE master
-- Now drop it
DROP DATABASE <database name>
Jacco Schalkwijk
SQL Server MVP
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eBGaP5K3EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I imported DBF files into a new SQL server database and I've been
> developing a VB.Net application.
> Now I would like to delete the current database and re-import a more
> current set of DBF files into SQL.
> Only problem, I can't seem to find a way to delete the database from MSDE.
> I tried the OSQL 'drop database <name>' but it complains the 'database is
> in currently in use'.
> I must be missing something simple.
> Thanks
> Richard
|||Thanks guys, you were right, there was a connection to the database.
At first I tried a 'use master' in qsql but got the same results. Then
it dawned on me that Visual Studio itself had a connection to the
database. Once I closed it, I could delete the database.
To be sure I understood it, I restored the database, checked in VS.Net
to see it, then I deleted it in osql while VS.Net was open (but no
connection), this also worked.
Thanks for you help!
Richard
Jacco Schalkwijk wrote:
> It is possible that there are still connections to the database. If you are
> on MSDE 2000 the following is a sure way to drop a database:
> -- Kick everyone out of the database
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> -- Go somewhere else yourself as well
> USE master
> -- Now drop it
> DROP DATABASE <database name>
>