LoginSignup
6

More than 5 years have passed since last update.

SQL Server 役立つSQLメモ

Posted at

乱数生成

SELECT ABS(Checksum(NewID()) % 9)

テーブル件数

SELECT o.NAME, i.rowcnt 
FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id 
WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY i.rowcnt desc

constraintsを無効にする

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

constraintsを有効にする

EXEC sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Index一覧を選択する

SELECT
     TableName = t.name,
     IndexName = ind.name,
     IndexId = ind.index_id,
     ColumnId = ic.index_column_id,
     ColumnName = col.name,
     ind.*,
     ic.*,
     col.*
FROM
     sys.indexes ind
INNER JOIN
     sys.index_columns ic ON  ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
     sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
     sys.tables t ON ind.object_id = t.object_id
--WHERE
--   ind.is_primary_key = 0
--   AND ind.is_unique = 0
--   AND ind.is_unique_constraint = 0
--   AND t.is_ms_shipped = 0
ORDER BY
     t.name, ind.name, ind.index_id, ic.index_column_id;

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6