SQL Server

PLAN A

select so.name as テーブル名, sc.name as 列名, st.name as データ型, sc.length as 長さ, sc.isnullable as NULL許可
from sysobjects so
join syscolumns sc on so.id=sc.id
join systypes st on sc.xtype=st.xtype
where so.type='U'
/* すべてのテーブルから取得したい場合は and so.name='tablename' を削除 */
and so.name='tablename'
order by so.name,sc.colorder

PLAN B

SELECT T.name, C.name, Y.name, C.max_length, C.precision, C.scale, C.is_nullable, ISNULL(I.index_id, 0) AS is_pk
FROM sys.tables T
LEFT JOIN sys.columns C ON C.object_id=T.object_id
LEFT JOIN sys.types Y ON Y.system_type_id=C.system_type_id AND Y.user_type_id=C.user_type_id
LEFT JOIN sys.index_columns I ON I.object_id=C.object_id AND I.column_id=C.column_id AND I.index_id=1
WHERE T.type='U'
/* すべてのテーブルから取得したい場合は and T.name='tablename' を削除 */
and T.name='tablename'
ORDER BY T.name, C.column_id

2011-08-10

2011 年 8 月のセキュリティ情報

Microsoft

緊急

重要

警告

2011-08-09

テーブルサイズを確認するSQLクエリ

SQL Server

CREATE TABLE #SPACES
([name][sysname],[rows][bigint],[reserved][varchar](18),
[data][varchar](18),[index_size][varchar](18),[unused][varchar](18))
DECLARE @table sysname
DECLARE C CURSOR LOCAL FOR SELECT name FROM sysobjects WHERE XTYPE='U'
OPEN C
FETCH NEXT FROM C INTO @table
WHILE (@@FETCH_STATUS=0)
BEGIN
INSERT INTO #SPACES EXEC sp_spaceused @table FETCH NEXT FROM C INTO @table END CLOSE C DEALLOCATE C
SELECT * FROM #SPACES
ORDER BY name
DROP TABLE #SPACES