查看远端数据库用户登录信息 sp_helpremotelogin 查看数据库下某个数据对象的大小 sp_spaceused @objname 查看某数据库下某个数据对象的索引信息 sp_helpindex @objname 查看某数据库下某个数据对象的的约束信息 sp_helpconstraint @objname 查看表的相关信息 方法1： sp_help 'TABLE_NAME' 方法2： sp_desc 参考我的博客MS SQL 模仿ORACLE的DESC 修复迁移服务器时孤立用户时 方法1： USE {目标数据库} EXEC sp_change_users_login 'Update_One', '{目标数据库已存在的用户名}', '{创建的登录用户名}' 方法2 Code Snippet - declare @cmd nvarchar(4000)
-
- set @cmd = N'exec [?].sys.sp_change_users_login @Action = ''Auto_Fix''
-
- , @UserNamePattern = ''qa''
-
- , @LoginName = null
-
- , @Password = ''abc'' '
-
- exec sp_msforeachdb@cmd
查看数据库数据文件情况 查看数据库实例各个数据库的数据文件信息 方法1： 选择某个数据库，然后单击右键属性...(后面我就不说了，不知道的自己百度) 方法2：SQL Code Snippet - SELECT database_id AS DataBaseId ,
- DB_NAME(database_id) AS DataBaseName ,
- Name AS LogicalName ,
- type_desc AS FileTypeDesc ,
- Physical_Name AS PhysicalName ,
- State_Desc AS StateDesc ,
- CASE WHEN max_size = 0 THEN N'不允许增长'
- WHEN max_size = -1 THEN N'自动增长'
- ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
- END AS MaxSize ,
- CASE WHEN is_percent_growth = 1
- THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
- ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
- END AS Growth ,
- Is_Read_Only AS IsReadOnly ,
- Is_Percent_Growth AS IsPercentGrowth ,
- CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)]
- FROM sys.master_files
查看单个数据库的数据文件信息： SQL 1：上面SQL加上查询条件 SQL 2： Code Snippet - SELECT Name AS DataBaseName ,
- Physical_Name AS PhysicalName ,
- type_desc AS FileTypeDesc ,
- State_Desc AS StateDesc ,
- (( size * 8.0 ) / 1024 / 1024 ) AS [Size(GB)] ,
- CASE WHEN max_size = 0 THEN N'不允许增长'
- WHEN max_size = -1 THEN N'自动增长'
- ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2)) + 'G'
- END AS MaxSize ,
- CASE WHEN is_percent_growth = 1
- THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'
- ELSE RTRIM(CAST(Growth AS CHAR(10))) + 'M'
- END AS Growth ,
- Is_Read_Only AS IsReadOnly ,
- Is_Percent_Growth AS IsPercentGrowth ,
- CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)]
- FROM sys.database_files ;
|
