sql2008导出数据表索引语句
在SQL Server 2008中，我们可以使用以下语句导出数据表索引：
1. 导出特定表的索引语句：
```sql
-- 导出表的索引语句
SELECT 'CREATE INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ix.object_id) + '(' + c.name + ')' AS IndexScript
FROM sys.indexes AS ix
INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id
INNER lumns AS c lumn_id = c.column_id AND ic.object_id = c.object_id
WHERE OBJECT_NAME(ix.object_id) = 'TableName'
ORDER BY ix.index_id
```
以上语句将输出指定表的所有索引语句，其中`TableName`为要导出索引的表名。
2. 导出数据库中所有表的索引语句：
```sql
-- 导出数据库中所有表的索引语句
SELECT 'CREATE INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ix.object_id) + '(' + c.name + ')' AS IndexScript
FROM sys.indexes AS ix
INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.
index_id
INNER lumns AS c lumn_id = c.column_id AND ic.object_id = c.object_id
WHERE OBJECTPROPERTY(ix.object_id, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(ix.object_id), ix.index_id
```
以上语句将输出数据库中所有表的索引语句。
3. 导出特定表的聚集索引语句：
```sql
-- 导出表的聚集索引语句
SELECT 'CREATE CLUSTERED INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ix.object
_id) + '(' + c.name + ')' AS IndexScript
FROM sys.indexes AS ix
INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id
INNER lumns AS c lumn_id = c.column_id AND ic.object_id = c.object_id
sql语句替换表中内容WHERE OBJECT_NAME(ix.object_id) = 'TableName' pe = 1
ORDER BY ix.index_id
```
以上语句将输出指定表的聚集索引语句。
4. 导出特定表的非聚集索引语句：
```sql
-- 导出表的非聚集索引语句
SELECT 'CREATE NONCLUSTERED INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ix.object_id) + '(' + c.name + ')' AS IndexScript
FROM sys.indexes AS ix
INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id
INNER lumns AS c lumn_id = c.column_id AND ic.object_id = c.object_id
WHERE OBJECT_NAME(ix.object_id) = 'TableName' pe = 2
ORDER BY ix.index_id
```
以上语句将输出指定表的非聚集索引语句。
5. 导出特定表的唯一索引语句：
```sql
-- 导出表的唯一索引语句
SELECT 'CREATE UNIQUE INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ix.object_id) + '(' + c.name + ')' AS IndexScript
FROM sys.indexes AS ix
INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id
INNER lumns AS c lumn_id = c.column_id AND ic.object_id = c.object_id
WHERE OBJECT_NAME(ix.object_id) = 'TableName' AND ix.is_unique = 1
ORDER BY ix.index_id
```
以上语句将输出指定表的唯一索引语句。
6. 导出特定表的包含多个列的索引语句：
```sql
-- 导出表的包含多个列的索引语句
SELECT 'CREATE INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ix.object_id) + '(' +
STUFF((SELECT ', ' + c.name
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论