SQL Server, Drop All Tables in Database Quickly
Just a quick and dirty way of dropping all the tables in a database, without dropping the database itself. Useful for when your writing data migration scripts and staging data and need to wipe out your development environment real quick ... or, when you've found that uber cool sql-injection-able site and want to wreak some havoc (I don't condone the latter, but I do think its funny from time to time when it happens to the 'big companies').
-
SELECT name INTO #tables from sys.objects where type = 'U'
-
while (SELECT count(1) FROM #tables) > 0
-
begin
-
declare @sql varchar(max)
-
declare @tbl varchar(255)
-
SELECT top 1 @tbl = name FROM #tables
-
SET @sql = 'drop table ' + @tbl
-
exec(@sql)
-
DELETE FROM #tables where name = @tbl
-
end
-
DROP TABLE #tables;
Need a Data Model, take a Data Model.
I was speaking with a co-worker last night; he's working on a project that requires a sample database for a demo he's making, and as with most demo's he was planning to use either the Northwind or AdventureWorks databases from Microsoft. I made the suggestion of 'googling' for 'sample database' and using something 'not so common' ... however, his requirement relies on 'standard', so he went with AdventureWorks.
I got curious this morning, and decided to run the query myself and came across this interesting website that has quite a large number of 'standard models' already laid out for you in UML Diagrams.
The website is maintained by Barry Williams and is simply called "Database Answers'.
It's not the prettiest site I've ever seen, in fact, it's quite ugly looking to be honest, but I'm not one to judge by looks ... I dug through and looked at a few of the Models presented and was quite impressed with the work put into them. Everything from ACL (Access Control) to Video Rental and Wedding Management schema's are available ... for FREE!
If your working on a website, or a new application that requires a database schema and your not quite sure how to design it yourself ... give this great web resource a try and check it out. The site even comes pack with Tutorials to show you how to work with the Models provided.
Can't beat that, eh?