Can I exclude certain tables and views from my database project in visual studio 2012 by wildcard?

Question

I would like to be able to do schema compare between my Visual Studio 2012 Database Project and a Development or Production database, however there are a number of tables which are dynamically created and I would like to exclude them from the compare by Table/View Name Mask. I.E. "TABLE_%" or "VIEW_%".

Does anyone know how I can achieve this with visual studio 2012? I could move these items to a different schema if necessary however it appears I'll have the same headache if I do.

Thanks in advance.

Giffer

Using a different schema would at least make it easier to exclude, but I don't think there's any definite way to exclude them by default. What's in these tables/views that they shouldn't be added to the project? If they're easy enough to switch schemas, consider moving them to their own DB and referencing them from there. – Boyhood

Thank you for the comment, They are dynamically created staging tables... I'd rather not have to have another DB to configure connections to just for staging. – Giffer

Perhaps a partial or composite project something like this would work: sqlblog.com/blogs/jamie_thomson/archive/2013/03/10/… - not ideal, but put your dynamic stuff in there as "same db, same server" and reference that. If it's separated out in its own dacpac for DB reference (or project), you should be able to exclude it from the deploy, but creating other objects may fail if those dynamic objects don't exist. – Boyhood

That's the challenge there are so many of them that it can't do the compare... runs out of memory on the visual studio box (where I have 12 GB of ram) can't see how splitting it into two partial projects would avoid this... Does it not still need to enumerate all the objects on the server to compare to at least one project of the partial db project? And I clearly cannot delete the dynamic tables from a production db... Meaning I have to back it up, and restore it to anther server before running a cleanup script to begin my compares (VERY time consuming) – Giffer

It does have to compare against the existing database so if you have a lot of objects in that one database you could have issues. It shouldn't have to go through the whole server, though. Can you extract the DB into a dacpac using SQLPackage? I have a reference to the command line towards the end of this article: schottsql.blogspot.com/2012/10/… - it may be a step in the right direction. Alternatively, have you tried Red-Gate's SQL Compare product? That might also be worth considering. – Boyhood

We have started using Red-Gates SQL Compare product... not that long ago it was enhanced to allow for better filtering of sql objects. – Giffer

Answer 1

NOT LIKE (
    SELECT *
    FROM Example
    WHERE Name 
        LIKE "Joh_%"
    )

Not sure if it's NOT LIKE () or WHERE NOT EXISTS ()

Ph