.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

Modify a table name without drop it and recreate it using T SQL

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server
 

Hi

How to modify a table name without drop it and recreate it using T SQL. Suppose the SSMS is not installed or reachable.

Thank you

 


The complexity resides in the simplicity


View Complete Post


More Related Resource Links

Excel Drop Table question

  
Can anyone tell me if the drop table syntax should delete only the data in a worksheet or clear the formatting as well. If not is there a way to clear all the formatting. Thanks

Need to Automate in SSIS Cube Drop and Recreate of Dimensions: Had to do on the Solution Explorer Ma

  
Hi SSAS Engineers: This question is in the context of a complete refresh of the SSAS cube. I'm OK on the SSIS approach thanks to TMcD and past successes (also thanks to him). The question is why did I have to manually drop/delete the dimensions in BIDS SSAS, then readd them with the cube wizard to pull in the dimensions with the same names, but new surrogate key relationships and more surrogate keys in total? We need to Automate in SSIS Cube Drop and Recreate of Dimensions. I originally had to perform this function on the Solution Explorer Manually in SSAS UDM, since the names are the same, but the structures and the data are changing for the dimensions on the refresh (and the fact table is subsequently increasing). Herein lies the problem. We had a keynotfound error in the cube processing, since the old keys were being kept. I had run Full processing on the dimensions, but since the dimensions have the same name (no drop old names and add new names), the old surrogate keys remained. The fact table had the old row count as well, since the surrogate key pipeline to look up the surrogate keys for the new dimensions only had the old dimensions, even though I truncated the fact table. Yes--I had already rebuilt the dimensions in SQL Server and the row counts were the new refresh row counts. The dimension counts were right according to refresh numbers after

Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


how can i drag and drop fields of table in teh desing surface

  

I am followint the insructions of this walkthrough of VS2003

http://msdn.microsoft.com/es-es/library/1as0t7ff(en-us,VS.71).aspx

adn in a part say this


  •  8   Find the authors node and expand it to show the fields in the authors table.
  •  9   Using CTRL+Click, select the au_id, au_lname, au_fname, and city fields.
  •  10  Drag these fields from Server Explorer onto the design surface
  • Now i am using VS2008 

    When i try to drag and drop i can not made it is maybe because of the differences between 2003 and 2008 versions

    how can do it in VS2008?

    Luis 


    Drop tables where table name is a parameter

      

    I'm trying to drop some tables and I created a curser to do so.

    The table name is a parameter.

    I get an error message when I run this code in the curser.

    Is there any way to run this code in a different way.

    I'm trying to drop databases as well where database name is a paramerter, can I do so?

     

     

    Declare

     

    @DatabaseName varchar(30)

    Declare

     

    @FullDatabaseName

    How to modify the drop-downlist in i top-meny

      
    How can I modify the drop-down-list i the top-meny so that i shows all the lists, sites ang pages within that entering?

    Cannot modify a column type in a table in SQL Serv 2008 Ent

      
    I used ot be able to do it when the same tables were part of Sql Express: to change the column type in SSMS but now I get an error. I have never seen it before. I created a large number of table in one DB and for some reason I cannot recall, some of them ended up with int type for volume column, others bigInt. They all appear to have been created from .NET C# code not manually. Now I am getting exceptions because my code expects Int64 across the board.

    This is the message that pops up when I try to save the change:

    Saving Changes is not permitted. The changes you have made require the following tables to be dropped and recreated. You have either made changes toi a table that can't be recreated or enabled the option prevents saving changes that require the table to be re-created.

    Apparently some option is set up wrongly in the Properties=>Options. the question is which one? I checked all Alter options and still cannot save the change.


    Thanks.
    AlexB

    Difference between truncate and drop table on database fragmentation

      

    I have a group of developers that are creating stored procedures, in them they are dropping a table and re-creating the table and then continuing with the processing.  My question is if there is any difference between the effect of dropping a table and truncating the table on database fragmentatiom.

     

    Thanks

    Pam 


    cascading drop down lists from single table

      

    my one table consists of fields (id, flying from, flying to). thus i have one drop down for flying from and another drop down for flying to. i'm hoping to cascade them so 2nd drop down values are dependant on the values from 1st drop down. is it possible to do this using a single table? or must i use two tables and link the id's from both? and do i write anyting in the SelectedIndexChange event? thanks...

    the sqldatasource code looks like this:

    Flying From:

            <asp:DropDownList ID="ddlFlyingFrom" runat="server" DataSourceID="SqlDataSource1"
                DataTextField="FlyingFrom" DataValueField="Id" AutoPostBack="True" OnSelectedIndexChanged="ddlFlyingFrom_SelectedIndexChanged">
            </asp:DropDownList>

            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
                SelectCommand="SELECT [Id], [FlyingFrom] FROM [Flights]"></asp:SqlDataSource>
          &nb

    Getting the values of drop down repeating table in info path

      
    HI how can i get all the values of a drop down repeating table, using XPathNavigator please reply me!

    Does dropping a table automatically drop its contraints and indexes?

      
    When I have a table that has foreign key constraints and/or index and generate a delete script with Management Studio, the script explicitly drops all constraints/indexes before dropping the table.  Is this necessary?  Or does dropping a table automatically "cascade delete" all that table's constraints, indexes, etc...?

    New user cannot modify table.

      

    Hi.

    I have made a new sql user and add him to my database role called MyRole,then i add permissions on the rolles in dbo schema permissions options,all of my data tables are in default dbo schema.But when i try to modify my data table i can't it say somthing like this:

    "You are not logged on as the database owner or system administrator.You might not be able to save changes to tables that you do not own.

    Certain edits require CREATE TABLE permission."

     

     

     

     


    Drag and drop field from db into HTML table cell possible?

      

    Hi

    I'm used to Dreamweaver where this is possible. I find it hard to believe that I would need to use a 1 column gridView control.

    Is there a way to drag one field from the db Explorer window onto my page and NOT get a grid view?

    Or alternatively, I used to be able to put something like this in ASP classic (years ago!) <%= Eval("User") %>

    Any help would be SO appreciated.

    Thanks



    How do you Drop Table in C# code behind?

      

    I have a stored procedure that builds a table.  I need to delete the table before running it or I get an object already exists error.  How do you code Drop Table in C# code behind.  I have am calling the SP with execute non-query statement.

    Thank you 


    SQL Server Replication Error Cannot drop the table

      

    Can any one help me on the below error.

    I have a transactional replication (pull). All of a shudden it failed to transfer the data. The error message is

    The concurrent snapshot for publication 'Woolworths Petrol - Filtered' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot A

    I tried to reinitialize the subscriber and it pops up the below message.

    Cannot drop the table 'table_name' because it is being used for replication. (Source: MSSQLServer, Error number: 3724)

    I don't want to drop the table.

    Please help.

    CHIN


    How can I drop and recreate a default constraint using smo?

      

    I'm almost there.

    The problem: Using smo, I'm having trouble re-creating a DefaultConstraint on a column after dropping it.
    Where this stems from: I need to change a column from nvarchar(x) to nvarchar(max).

    (The ms help is useless, showing no examples for working with default constraints. No searches show people using smo to solve this.)

    The conceptual problem is that you can't change a column's size if a "DefaultConstraint" exists on the column. You must drop the DefaultConstraint, then change the size of the column, and then re-create the DefaultConstraint.

    Variables not shown: The variable "AColumn" below has been populated with a smo column object (Name:"SsGradeString"), and the variable "Size" is the desired size to change the column to.

    The error occurs on AColumn.AddDefaultConstraint(DName), error message:"Column SsGradeString already has a default."

    I tried doing Table.Alter, Column.Alter, DefaultConstraint.Alter (after the .Drop), but I get: "Cannot access properties or methods for the Microsoft.SqlServer.Management.Smo.DefaultConstraint '[DF_ADVSessions_SsGradeString]', because it has been dropped."

    'preserve existing values for re-creation of DefaultConstraint
    'NOTE: AColumn is the column we are working with and Size is the new size to set.

    Categories: 
    ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
    Sql Server  SharePoint  Silverlight  Others  All   

    Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend