Dropping scripts into sql 2005

Something that really irritates me with sql2005 is when I try to drag and drop a script into sql2005 I have to relog basically. Say I'm dropping in 20 different scripts, I have to relog and select the database every time for each script. Sql2000 did not do this, I drag and drop in scripts and whatever database I was currently in, it would use. Is this just a setting that I need to change or is it a horrible "enhancement"?

Thanks for your help.

Hi,

no I think currently this is not possible, one query windows per query will be opened and prompt you for the information where to connect to.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

DROP Temp Table or any scripts

Hi,

What control can I use to put any scripts like that?

ExecuteSQLTask lets me do just sql query.

I need to have something like this.

USE [DB1]

GO

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

DROP TABLE [dbo].[Lookup]

or Create

CREATE TABLE [Lookup] (
[MD_ID_Old] INTEGER,
[MD_ID_New] uniqueidentifier
)

I need to find the way to use scripts in SSIS.

Thanks.

Have you tried your sql statements in ExecuteSQLTask? This components exists for executing any sql statement. There is a SourceType property that allows you to point to a file [your script] and you can execute the sql statements that way.

Hope this helps...

Senthil

|||Thanks.

Drop all objects

HI,

Been poking around in sysobjects and information_schema.routines trying to work out how to best write scripts that will drop all specific objects from a database.

That is, scripts to drop all tables, views, stored procs, functions ( FN, IF, TF ) but can't seem to figure out appropriate way to do it.

A pointer on how to drop all of any one of the above object types would be greatly appreciated and I should be able to work out the others.

Further, when executing multiple scripts I am writing scripts like this...

ddl_batch.sql

Code Snippet

:R table1.sql

:R table2.sql

:R ufn_func1.sql

:R ufn_func2.sql

:R view_table1

:R view_table2

:R usp_proc1.sql

:R usp_proc2.sql

And executing via:

sqlcmd -S server\instance -i ddl_batch.sql

This is to maintain individual object type scripts, and then to execute them together in dependency order, rather than executing one monolithic batch script. Is this a reasonable way to go about it or is there a better way?

Many thanks in advance for your help.

Compose your SQL Strings on the fly using something like the following:

SELECT 'DROP TABLE [' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'),'IsMSShipped') = 0 --You do not want to drop the MS Shipped, right ;-) ?

You can use the output and store it in a file or execute it on the fly. Make sure that you first frop the foreign keys on the tables, then drop the tables, then the views, afterwards the functions.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Once again Jens many thanks for your help.

Drop all column extended propterties

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?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)

Drop all column extended propterties

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?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)