|
Home > Archive > MS SQL Server > May 2005 > RE: Copying tables, finding number of columns in a table, & size of da
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
RE: Copying tables, finding number of columns in a table, & size of da
|
|
| Alejandro Mesa 2005-05-23, 1:23 pm |
| > 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.
There is not a single way to do this. You can generate the script of table
tableA from EM, save it into a file. Open the file in QA, change all
references to tableA by tableA_copy. Before creating the indexes, do an
INSERT INTO TABLEA_COPY c1, ..., cn SELECT c1, ..., cn FROM TABLEA. Run the
script.
> 2) I know how to figure out how many rows a table has, how do you figure
> out how many columns?
See information_schema.columns in BOL.
Example:
select count(*)
from information_schema.columns
where table_schema = 'dbo' and table_name = 't1'
> 3) Where does it say how large in terms of disk space (data) a table is?
See sp_spaceused in BOL.
Example:
use northwind
go
exec sp_spaceused 'dbo.orders'
go
> 4) Can you determine how large in terms of disk space only selected
> columns within a table are?
See "Estimating the Size of a Table" and "Estimating the Size of a Table
with a Clustered Index" in BOL.
It is good that every table in the db has a clustered index, so you can
create an indexed view just with the columns in question and use sp_spaceused.
Example:
use northwind
go
create view dbo.v1
with schemabinding
as
select orderid, customerid, orderdate
from dbo.orders
go
create unique clustered index ix_u_c_v1_orderid on dbo.v1(orderid asc)
go
exec sp_spaceused 'dbo.v1'
go
drop view v1
go
AMB
"Sugapablo" wrote:
> 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 ]
>
>
|
|
|
|
|