Copying tables, finding number of columns in a table, & size of data in selected colum

I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all
its indexes and data in the same database as table A_copy.
2) I know how to figure out how many rows a table has, how do you figure
out how many columns?
3) Where does it say how large in terms of disk space (data) a table is?
4) Can you determine how large in terms of disk space only selected
columns within a table are?
[ Sugapablo ]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
[ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
SELECT * INTO A_copy
FROM A
Note: PK and FK and any indexed columns will migrate their data, but the
PK, FK and indexes themselves will not be recreated. You will have to do
that yourself.

> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
Take a look at the INFORMATION_SCHEMA views.
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<tablename>'

> 3) Where does it say how large in terms of disk space (data) a table is?
You can start with sp_spaceused 'objname'
EXEC sp_spaceused 'SomeTable'

> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
Not easily. You would need to get the width of the column (average width
for variable length columns) and multiply that by the number of rows in the
table. There are other factors to include, however, this should get you
reasonably close.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi,
I'm trying to figure out how to do a few things in MS SQL Server but can't
Google up what I need or find it in the docs that I have.
1) How do you simply copy a table. I just want table A copied with all its
indexes and data in the same database as table A_copy.
Generate Script with dependant objects using enterprise manager, execute
that in destination
database and use DTS to transfer the data.
2) I know how to figure out how many rows a table has, how do you figure out
how many columns?
select count(*) from syscolumns where object_name(id)='table_name'
or
Query the same on INFORMATION_SCHEMA.COLUMNS VIEW.
3) Where does it say how large in terms of disk space (data) a table is?
sp_spaceused <table_name>
4) Can you determine how large in terms of disk space only selected columns
within a table are?
You have to manually calculate based on usage and alloctions for each field.
Thanks
Hari
SQL Server MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.23.16.12.37.486877@.REMOVEsugapabl o.com...
> I'm trying to figure out how to do a few things in MS SQL Server but can't
> Google up what I need or find it in the docs that I have.
> 1) How do you simply copy a table. I just want table A copied with all
> its indexes and data in the same database as table A_copy.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
> 3) Where does it say how large in terms of disk space (data) a table is?
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
>
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>