Tuesday, July 15, 2014

Demo preparation - Removing unwanted Schema's from AdventureWorks

Those of us who's roles require them to conduct demonstrations and presentations of SQL Server regularly will be familiar with the AdventureWorks databases. I must admit this has been my go to demo database for a while now. However some times the whole schema is just too much and all I really want is a subset of the schema for a demonstration.

The problem is because of the number of Constraints, Foreign Keys, and Views in the AdventureWorks schema this is no simple task (as it shouldn't be). I was faced with this issue today and so have created the following statements to help with generating the drop statements. These are based on the schema's that the objects belong to. For my needs I only wanted the HumanResources and Person schemas.

Important... backup first if you care about your data and..... DO NOT DO THIS IN PRODUCTION.....


--Constraints
SELECT sys.schemas.name as 'Schema'
 , sys.tables.name as 'Table'
 , sys.check_constraints.name as 'Constraint'
 , 'ALTER TABLE [' + sys.schemas.name + '].[' + sys.tables.name + '] DROP CONSTRAINT [' + sys.check_constraints.name + ']' AS 'Drop Stmt'
FROM sys.check_constraints
INNER JOIN sys.tables ON sys.tables.object_id = sys.check_constraints.parent_object_id
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'

 
--Foreign Keys
SELECT sys.schemas.name as 'Schema'
 , sys.tables.name as 'Table'
 , sys.foreign_keys.name as 'ForeignKey'
 , 'ALTER TABLE [' + sys.schemas.name + '].[' + sys.tables.name + '] DROP CONSTRAINT [' + sys.foreign_keys.name + ']' AS 'Drop Stmt'
FROM sys.foreign_keys
INNER JOIN sys.tables ON sys.tables.object_id = sys.foreign_keys.parent_object_id
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'


--Views
SELECT sys.schemas.name as 'Schema'
 , sys.views.name as 'View'
 , 'DROP VIEW [' + sys.schemas.name + '].[' + sys.views.name + ']' AS 'Drop Stmt'
FROM sys.views
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.views.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'


--Tables
SELECT sys.schemas.name as 'Schema'
 , sys.tables.name as 'Table'
 , 'DROP TABLE ' + sys.schemas.name + '.' + sys.tables.name  AS 'Drop Stmt'
FROM sys.tables 
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
 AND sys.schemas.name <> 'HumanResources'
 AND sys.schemas.name <> 'Person'
ORDER BY sys.schemas.name, sys.tables.name 




Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

6 comments:

  1. replica cartier watches is the renowned French jeweller and watchmaker with a history of pioneering design and a drive for excellence. The replica calibre de cartier watchesfirst references to Cartier wristwatches date back to 1888. Late in the 19th century it was pocket and chatelaine watches that were most popular, but Louis Cartier saw a future in timepieces that could be worn on the wrist.

    ReplyDelete
  2. Fake Cartier watches come in all the fake watches prominent Cartier styles: Tortue, Tank, Santos, Ballon, and replica cartier watchesBaignore to mention a few. These come in a wonderful number of gorgeous gents and womens designs. Our fake Swiss Cartier watches are exact forgeries of the genuine ones, down to the detailing that includes coloured knobs, diamonds encrusting, and stainless-steel cases. These specific replications . are great for showing your love for quality whether at the workplace or perhaps in a social setting.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Buy a discounted IWC watch from this Time and get free shipping. Large selection in stock. replica watches UKIWC watches, is an iconic brand. The IWC Portuguese, replica IWC watches sale a collection that includes the IWC Portuguese automatic and IWC Yacht Club chronograph is legendary. IWC Schaffhausen is also known for the IWC Aquatimer, Top Gun, Big Pilot watch, Mark XVII, Pilot's Watch, Spitfire chronograph and their Big Pilot Watch, the St-Exupery.

    ReplyDelete
  5. Montblanc MeisterstückClassique is not surprisingly regarded as one replica mont blanc ballpoint pen of Montblanc's top ten fountain pens, replica great characters edition and perhaps the most iconic of the Montblanc writing series. This pen is coated with precious black resin and has a bright white star badge. It is a must-have for collectors and has a well-known name, with German as a "masterpiece". Each pen has a separate serial number, which means that no two pens are the same. Similarly, each nib of Montblanc is individually stamped after manual testing by Montblanc's master craftsmen.

    ReplyDelete
  6. Founded in 1905, Rolex watches have been in the uk replica rolex field of innovation for more than a century. The fake cosmograph daytona watches first certified precision chronograph, the first self-winding watch and the first effective waterproof case are milestones in Rolex design. Today, the Rolex brand is immediately recognized as a symbol of global reputation, luxury and innovation. Buying a Rolex watch means you are buying a watch history. Here we have a large catalog of used Rolex special watches for sale.

    ReplyDelete