Apple&Ben@台北花博II(2010-12-19)

找出SQLServer某個Database裏每個table的使用量

---引用 http://blog.tenyi.com/2009/11/sqlservertable.html

set nocount on

print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'

select
[FileSizeMB] =
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB] =
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB] =
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName] = isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name

create table #TABLE_SPACE_WORK
(
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)

create table #TABLE_SPACE_USED
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME sysname not null ,
TABLE_ROWS numeric(18,0) not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)

create table #TABLE_SPACE
(
Seq int not null
identity(1,1) primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MB numeric(18,4) not null,
USED_GB numeric(18,4) not null,
AVERAGE_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null,
AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null,
)

declare @fetch_status int
declare @proc varchar(200)
select @proc = rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
TABLE_NAME =
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
order by
1

open Cur_Cursor

declare @TABLE_NAME varchar(200)

select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from Cur_Cursor
into
@TABLE_NAME

select @fetch_status = @@fetch_status

if @fetch_status <> 0
begin
continue
end

truncate table #TABLE_SPACE_WORK

insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'


-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME

insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK

end --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW

)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB =
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW =
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED =
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA =
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE =
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED =
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE

SQL SERVER-取得資料庫所有資料表，取得資料表所有欄位

-- 引用 http://www.wretch.cc/blog/newmonkey48/7609381

SELECT
a.TABLE_NAME as 表格名稱,
b.COLUMN_NAME as 欄位名稱,
b.DATA_TYPE as 資料型別,
b.CHARACTER_MAXIMUM_LENGTH as 最大長度,
b.COLUMN_DEFAULT as 預設值,
b.IS_NULLABLE as 允許空值,
(
SELECT
value
FROM
fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', a.TABLE_NAME, 'column', default)
WHERE
name='MS_Description'
and objtype='COLUMN'
and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME
) as 欄位備註
FROM
INFORMATION_SCHEMA.TABLES a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON ( a.TABLE_NAME=b.TABLE_NAME )
WHERE
TABLE_TYPE='BASE TABLE'
ORDER BY
a.TABLE_NAME, ordinal_position

Apple&Ben@台北花博I(2010-12-12)

三峽老街@2010-11-21

新竹麥克田園@2010-11-16

Apple&Ben@大溪花海農場(2010-10-31)

Apple&Ben@白木屋文化館(2010-10-24)

Apple&Ben@手信坊文化館(2010-10-17)

Apple&Ben@泰山辭修公園momo表演(2010-09-25)

Apple&Ben@台東日輝渡假村(2010-07-16)

Apple&Ben@台東日輝渡假村(2010-07-15)

Apple&Ben@台東日輝渡假村(2010-07-14)

MS SQL 取日期

顯示本月第一天的語法（不含時間的部分）
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
顯示上個月最後一天（不含時間的部分）
select DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))
顯示這個月最後一天（不含時間的部分）
select DATEADD(mm, 1, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)))
取得目前月份的數值，若小於 10 則補 0 ( 回傳字串格式 ) ( 例如: 2008-04-11 會回傳 04 )
SELECT REPLACE(STR(datepart(mm, getdate()), 2, 0), ' ', '0')
顯示上個月第一天（不含時間的部分）
DATEADD(mm, -1, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

刪除資料庫中所有資料表的資料

[code:tsql]
select
distinct 表格名稱
into #LISTTABLES
from
(
SELECT
Top 100 PERCENT
a.TABLE_NAME as 表格名稱
FROM
INFORMATION_SCHEMA.TABLES a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON ( a.TABLE_NAME=b.TABLE_NAME )
WHERE
a.TABLE_TYPE='BASE TABLE'
and a.TABLE_NAME != 'sysdiagrams'
ORDER BY
a.TABLE_NAME, ordinal_position) c;


declare
@tablename nvarchar(max)

declare icur cursor static for select * from #LISTTABLES

OPEN icur

fetch next from icur into @tablename
while(@@FETCH_STATUS=0)
BEGIN

-- 使用 TRUNCATE 最快，且會將表格中的自動編號欄位歸零
-- 但若表格有被參考 Foreign Key 的話，會無法使用 TRUNCATE 指令
exec ('TRUNCATE TABLE ' + @tablename);

-- 如果 TRUNCATE 不成功，還可以用 DELETE 刪除所有資料
exec ('delete from ' + @tablename + ' where 1=1');

-- 使用 DELETE 不會將自動編號的欄位歸零，使用 DBCC CHECKIDENT 指令歸零
exec ('DBCC CHECKIDENT(''' + @tablename + ''', RESEED, 0)');

FETCH NEXT FROM icur INTO @tablename
END

close icur
deallocate icur

drop table #LISTTABLES
[/code]

Apple&Ben@大安森林公園 Part II(20100101)