有用的数据SQL脚本(做数据字典比较有用哦)
select [name] from [sysdatabases] order by [name]
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
查询一个数据库中的所有表
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select [id], [name] from [sysobjects] where [type] = 'u' and status>0
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
都知道[type] = 'u' 是用户表,[type] = 's'是系统表吧。
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
根据上个语句的查询结果然后再
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
查询一个表中的所有字段(我尝试不用[ID],而用[name]去查,结果报错了)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select [name] from [syscolumns] where [id] = 1931153925
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
查询表结构
SELECT table_name,column_name,column_default,
is_nullable,data_type,character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
SELECT table_name as '表名',column_name as '列名',data_type as '类型',
character_maximum_length as '长度',column_default as '默认值',is_nullable as '是否为空'
FROM INFORMATION_SCHEMA.COLUMNS
where table_name in(select name from sysobjects where xtype='u' and status>0)
order by table_name
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
将孤立的用户名关联
sp_change_users_login 'report'
sp_change_users_login 'update_one','OAS','OAS'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
sp_change_users_login 'Auto_Fix', 'test', NULL, 'testpassword'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
posted on
2008-06-27 09:47
ringwang
阅读(
644)
评论()
编辑
收藏
举报