CREATE script that filters out empty columns

All:
Say I need to duplicate a table, but the CREATE script must only
include those columns of the table where the value in ALL the available
rows is not null. For an ad-hoc exercise (one or two tables), this is
easy, but for duplicating, say, 90 tables with the empty columns
filtered out, I assume I need an SP that uses each table's metadata to
test each column individually, for each table. A temp table could then
keep the name of those non-empty columns, and the script would
recreate the new table's script from the resulting set.
If someone can suggest a script to do this, I'll be more than happy...BTW, I do know that information_schema.columns is involved... I know
what the logic should be, I simply don't know how to translate that
logic into T-SQL well enough to be efficient... and maybe VB.NET should
be involved, instead should be something like:
(code to write the beginning of the CREATE TABLE statement, plus the
first bracket)
For all tables in the database
For each column in current_table
SELECT DISTINCT (current_column) , COUNT(*) FROM current_table
GROUP BY (current_column)
If (COUNT(*) >= 1 AND (individual value in the column) <> NULL
then /* This implies that the only value there is not NULL */
(write the name of current_column to a file, plus its data
type and width, and a
comma if not the last column)
end if
next column
next table
(write the closing bracket)
Any suggestions?

CREATE PROC Question

Gurus help me:
Here's the scenario...
Have a SP in the Master DB that creates a NEW, empty DB using a name I give it on the fly.
I need to Create a SP in that NEW DB.
Everything will be called from a DTS Package.
How to do this?
RobbieDCan you tell us why you are doing this...

Just seems like a very bad idea...

Are you talking about MSDE?|||I'll second the notion that this sounds like a bad idea. It can certainly be done, but there are lots of things that you can do, but shouldn't!

-PatP|||"Location: In front of the computer"

LOL

Moe, Larry look, it's a DBA with a sense of humor...

Why I oughtta...|||Hey Brett:

It's to automate Replication (see my other posts).

We have a subjective DB name @. the Publisher that has to be acquired, then replicated EXACTLY.

This procedure will create the Subscription DB & then create the SP to complete the the subscription itself.

Clear as Mud?!?!

(BTW - GREAT reply to the recruiter. He suddenly became less verbose!)|||Sounds ambitious...how many subscribers do you expect to have...|||Just a single other instance...But we'll do this MONTHLY.

HOWEVER, we have to duplicate the process in reverse later on.

Ambitious pretty much hits the nail on the head!!!|||I wouldn't support that kind of design, but here's your answer:

use model
go
create procedure <your_procedure>...
go|||Just 1?

That's a lot of effort to think outside the box...why complicate things?|||Got any suggestions?|||Did you get it or I have to explain it?|||Sorry rdjabarov:

I see where you're going, but if I want this code in a SP OR for that matter in an ActiveX module of a DTS, I can't get away with "USE".|||But your only replicating monthly?

Why not dump and restore?

MAYBE 10 lines of code

Done!|||robbied111,

You don't call this code from anywhere, you write it in QA. Since you already have the code to create a database, you won't have to worry about creating a procedure every time your ASP code creates a database, the procedure will already be there...Can you try it at least?|||THANKS All.

I'll do some more work & let you know how I fare.

RobbieD

(It's past 5pm here - time to blaze!!!)

Create Database in Management Studio

Hi

Does anyone know how to create an empty database in Management Studio. Or how to get rid of tables that are in the default database that the default script creates using a single command.

By deault the database is created with all the tables from Model database, and to get rid of tables individually, one has to go and look at all the dependencies before they can be deleted.

Thanks

Alvin

Hi,

once we did that our own. The model database could not be changed for some reason I can′t remember :-) So we wrote a stored procedure which copied a template to a specified folder and attach the database to the server. The template database was clear from the users / objects we did not want to have in the new database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de