Is there a table designer for VS2010 database project?

Question

Am I missing something here? It seems that the only options to create a new table in a database project in VS2010 is:

Create a table object as a file, then create all constraints (defaults) as separate files, then create each index as a separate file, and primary key as a separate file and on and on...

Or

Create the entire table schema using the table designer in SSMS and then use the schema compare tool to create a single monolithic file of SQL statements for each element of the table and copy each block of code to a newly created file in VS.

This question was asked 2 years ago and I'm hoping the answer has changed. Please tell me there's a hidden table designer for the database project in VS2010 that I have just overlooked.

Capitate

Answer 1

I'm pretty sure there isn't one!

Can I ask why you need a table designer over creating and modifying creation script files for your new objects? Is there anything that this doesn't give you that a designer would?

Urine

@David, I prefer a designer in this case for the same reason I use other coding tools (like VS), rather than coding in notepad. Convenience, increased productivity and reliability. Hopefully, you'd agree that some code is so "Boiler-plate" that it is generated and maintained quicker and more reliably by a tool than by a human. When "under-the-gun", I'd rather spend my time and thought processes on business logic than mind-numbing "ALTER TABLE xxx ADD CONSTRAINT xxx blah, blah blah" statements. – Capitate

@David, after looking at your profile and seeing that you work for Red Gate, I might add that other coding tools I use for similar reasons include: Red Gate's SQL Prompt, Refactor, Data Gen, Docs and previously, Packager. You, out of all, should understand my desire for a table designer. – Capitate

The whole point about VS2010's offline script model is that it doesn't require you to form difficult-to-remember ALTER statements to modify your objects. You're defining the CREATE scripts (from a template starting point) and when this is deployed to a database, the hard stuff is done by the engine underneath. – Urine

Many users have been asking us to 1) support the VS database project and 2) develop a designer, so we're actively looking into it. At the moment the way SQL Source Control works is to let you develop on a real database, and therefore you can benefit from using the existing designers in SSMS. Sadly SQL Source Control doesn't (yet) support the VS2010 database project, so if you're stuck with that, your options are sadly limited, at least for now. – Urine

@David, Thanks for taking the time to respond. Although I really like your products, I think for the $$$$$ MS is charging for VSTS with the Datadude module, it should at least have the programing features of SSMS without the need for 3rd party plugins. Regarding your first response to my response, I'm a little mystified. I'm not aware of a "CREATE" statement for table constraints... The only one I know of is within the "ALTER TABLE" statement (as alluded to in my first response). I'm fully aware the requirement to only use "CREATE" statements. – Capitate

Yes, you're entirely right. Constraints can only be represented as ALTERs to a table, so I acknowledge that everything's not as simple as it could be. I can't really speak for Microsoft, so I don't understand why the designers aren't present. However, if Red Gate decides to support the VS 2010 Database Project, I'll post to this thread and let you know. That's as much as I can contribute from where I'm standing. – Urine

Seconded here. I make all my relationships and tables in the database diagram designer. I can't imagine why someone would even have to ask why this is easier and more reliable than typing out entire scripts? I am kind of mystified by this response, am I in an alternate universe? – Bloodstained

Answer 2

The way I use the database project in VS2010 is:

1. Create everything with SQL Server Management Studio.
2. Synchronize it into my database project.
3. When I need to change something, do it in SQL Server Management Studio.
4. Use Schema Comparisons to synchronize your database project.

Remscheid

Answer 3

I just noticed that VS 11 Beta now includes a designer, although it is rough around the edges (relationships, for example, still need to be typed by hand).

Trod

Hey, thanks for the response. That sounds encouraging! Will have to check that out. We've had other serious issues with particularly, updates and constraints. I have been out of that part so long, I don't know the exact issues, but it's not nearly as useful as we thought it would be when we committed to it. Hopefully they've addressed those issues as well. – Capitate

Answer 4

Wow... can't believe no one has taken the time to answer this in all this time. Here's a sample of table creation script with some simple constraints.

    CREATE TABLE [User]
(
    UserID              INT             NOT NULL    IDENTITY (1,1), 
    UserName            NVARCHAR(20)    NOT NULL,
    UserPassword        NVARCHAR(20)    NOT NULL,
    EmailAddress        NVARCHAR(50)    NOT NULL,
    Location            NVARCHAR(100),
    MobileNumber        VARCHAR(10),
    CreatedDate         DATETIME        NOT NULL    
        CONSTRAINT User_CreatedDate_DF  DEFAULT                 (GETDATE()),
        CONSTRAINT User_UserID_PK       PRIMARY KEY CLUSTERED   (UserID),
        CONSTRAINT User_UserName_UQ     UNIQUE                  (UserName),
        CONSTRAINT User_EmailAddress_CK CHECK                   (EmailAddress LIKE '%@%.%'),
        CONSTRAINT User_MobileNumber_CK CHECK                   (MobileNumber LIKE '[2-9][0-9][0-9][2-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)

You can use functions to embed in your check constraints, but again, this is a simplistic exaxmple.

Rudbeckia

Thanks for taking the time to give an answer, but, unfortunately, adding constraints as part of the CREATE TABLE statement isn't allowed in the VSDB project and even if it was, it would defeat the purposes in having separate files for each database object, which are: 1. protection via Source Control and 2. having individual objects to compare in the upgrade process. My beef is that creating those individual objects as files is a time consuming exercise. With a table designer, I could create a table and all constraints and related objects as files in a matter of minutes. – Capitate

And BTW, I'm well aware of how to write a table creation script with contraints, but thanks just the same... I too am amazed that I've only gotten 2 answers on this. Especially surprised not to get an answer from someone from MS, maybe on the Visual Studio team. Oh well.. – Capitate

Answer 5

As I commented here, the VS2010 reference states that there exist a Table Designer in this document.

But for some reason, no matter what kind of project I create (Server project 2008/2005, database project 2008/2005) I can't get the Table Designer being shown.

Gilgamesh

I think that's basically the table designer from SSMS. I think it's designed for interaction with a SQL server instance or an MDF file, not for a VSTS database project. It would be no different than just opening up SSMS (or the free version), designing the table and copying the SQL code the tool generates to the project files. You'd still have to manually maintain the code for the constraints as separate code files. You and I both believe it would be better for a design tool to maintain all table related files. I wouldn't doubt if the DB project dies on the vine, in lieu of EF. – Capitate