extract schemas - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

extract schemas

Author

Topic

dtrivedi
Posting Yak Master

153 Posts

Posted - 2010-12-13 : 10:25:12

my boss asked me to extract schemas off 4 tables on to the local drive...I am confused as to what he means. does he just want me to copy the table to the local drive?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-13 : 10:30:12

I'm guessing he wants you to script the create statements.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2010-12-15 : 03:51:20

I have a question same ,Is it possible to take thebackup of database which contain only all database objects, like tables, SP, functions etc? it does not contain any data

the_amol
Starting Member

6 Posts

Posted - 2010-12-17 : 03:05:51

Definitely your boss is not asking for backup.
Schema basically is structure of table i.e. Table name, column names, column datatypes.

Either you can use the scripting method that i mentioned here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154291 please check.

or u can manually check each and every table using the query

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%%'

-or-

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = ''

Thanks & Regards
Amol Verma
http://cybertechhawks.wordpress.com

proud_child
Starting Member

6 Posts

Posted - 2010-12-20 : 06:22:44

If you need the entire DB structure, I think it is much faster to restore, then truncate all tables.

SCRIPT :

select 'truncate table '+name from sysobjects where xtype = 'U'

run all scripts in the result set.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-26 : 20:22:46

quote:
Originally posted by proud_child

If you need the entire DB structure, I think it is much faster to restore, then truncate all tables.

Depends on the size of the database. Think of a multi TB.

See this for scripting objects using dmo
http://www.nigelrivett.net/DMO/DMOScripting.html

I suspect it has been done now tough as it should be a 5 minute task.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources