SQL Management Studio Won't Recognize a Table Exists After Scripted Create

SQL Management Studio won't recognize a table exists after scripted create

Press Ctrl-Shift-R to refresh the intellisense.

Can't see created tables in Object Explorer - Microsoft SQL Management Studio

This would happen if you have the tables node open in object explorer and don't refresh after running your DDL. It is annoying that SSMS doesn't autorefresh explorer after DDL. Refresh is available via the right-click context menu in object explorer.

Using table just after creating it: object does not exist

Without using GO, programmatically, you would need to make 2 separate database calls.

Right click script alter table disabled in SQL Server Management Studio

Use the "Design" function in SQL Server Management Studio to generate the ALTER script for you:

• Right-click on the table you want to alter and choose Design.
• Add new columns, change field types, set your fields to accept NULLS or not, etc.
• Once you are done, click the Generate Change Script toolbar button (or right-click on any column or in the white space). This button looks like a floppy disk hovering over a piece of curled paper
• Save the result to a text file

Depending on how you have your SSMS options set, this may not be available initially if the changes require tables to be dropped and re-created. To change this option, on the Tools menu, click Options, expand Designers, and then click Table and Database Designers. Select or clear the Prevent saving changes that require the table to be re-created check box.

SQL Management Studio can't drag table / object from Explorer to Query Editor

First... does drag drop still work on other programs that aren't SSMS? I doubt that this is a feature you can turn on/off within SSMS. It probably is something deeper in the OS.

If so - is the shift key or ctrl key on your keyboard stuck down? I would also check options in the start menu. Disabling drag/drop and right/click can be done at the system level or by group policy - but again it would affect more than just SSMS.

I've also heard of various viruses disabling drag/drop but again, it seems strange it would only affect SSMS.

I cannot find my CREATE VIEW table after refreshing even after Commands completed successfully. message. (SQL)

First of all, make sure that you have expanded the correct database in the Object Explorer tree view in the left pane and that the SQL editor window is connected to the correct database. (The editor's current database is displayed in the toolbar, just below the main menu.) Being connected to the wrong database (often the master database) is a regular mistake by myself, anyway.

You should be aware that the Object Explorer pane is not automatically updated when you make modifications by executing an SQL query or script. You may update the tree view manually by right-clicking the desired node (in your case the Views node) in the tree and clicking Refresh in the context menu.

Also note the error in the SQL editor, indicated by a red underline. It may be the case that the local IntelliSense cache is corrupted or not updated correctly. You can update it manually. In the main menu, go to Edit->Intellisense->Refresh local cache. Shortly after clicking it, the error message (red underline) should disappear.

Forcing Management Studio to use ALTER TABLE instead of DROP/CREATE

You can't change the behaviour, it's just that the default script it creates is not always the most efficient. It creates scripts in a format it knows will work, although frequently the results will be slow and resource-heavy. I recommend you get used to creating the script for all changes yourself as you can better optimize it. There's nothing wrong with what you've created (providing you have no existing constraints/dependencies on MyCol2 that would be invalidated by it becoming a nullable int)

Saving changes after table edit in SQL Server Management Studio

Go into Tools -> Options -> Designers-> Uncheck "Prevent saving changes that require table re-creation". Voila.

That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn't trust you, you need to say "OK, I know what I'm doing, now let me do my work."

Compose a query that runs whether or not the table exists

why not use a function?

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create FUNCTION [dbo].[fn_checktable] 
(
    -- Add the parameters for the function here
    @checktable nvarchar(max)
)
RETURNS nvarchar(100)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result int

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['+@checktable+']') AND type in (N'U'))
        set @result = 1 
    else 
        set @result = 0

    endsave:
    -- Return the result of the function
    RETURN @result

END

GO

then use like

select case when dbo.fn_checktable('YOURTABLENAME') = 1 
    then (select top 1 'whatever' from YOURTABLENAME ) 
    else 'nothing' end as something

the result from select dbo.fn_checktable('YOURTABLENAME') is 1 or 0.

But, maybe i missunderstood. You wrote "a count of rows"...hmmm, i think i need more input.

---

Related Topics