[转]SQL Server 结构读取

本文来自：http://www.cnblogs.com/StrangeCity/p/4352753.html

-- 本文来自：http://www.cnblogs.com/StrangeCity/p/4352753.html
-- 获取一个连接上的所有数据库信息
SELECT * FROM master..sysdatabases
-- 获取一个数据库上的所有表字段信息
select  
     [表名]=c.Name, 
     [表说明]=isnull(f.[value],''),  
     [列序号]=a.Column_id,  
     [列名]=a.Name,  
     [列说明]=isnull(e.[value],''),  
     [数据库类型]=b.Name,    
     [类型]= case when b.Name = 'image' then 'byte[]'
                  when b.Name in('image','uniqueidentifier','ntext','varchar','ntext','nchar','nvarchar','text','char') then 'string'
                  when b.Name in('tinyint','smallint','int','bigint') then 'int'
                  when b.Name in('datetime','smalldatetime') then 'DateTime'
                  when b.Name in('float','decimal','numeric','money','real','smallmoney') then 'decimal'
                  when b.Name ='bit' then 'bool' else b.name end ,
     [标识]= case when is_identity=1 then '是' else '' end,  
     [主键]= case when exists(select 1 from sys.objects x join sys.indexes y on x.Type=N'PK' and x.Name=y.Name  
                         join sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id)  
                     then '是' else '' end,      
     [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'  
                   when b.Name='xml' then '2^31-1字节/2G' 
                   else rtrim(a.[max_length]) end,  
     [长度]=case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1' 
                 else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) end,  
     [小数位]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),  
     [是否为空]=case when a.is_nullable=1 then '是' else '' end,      
     [默认值]=isnull(d.text,'')      
 from  
     sys.columns a  
 left join 
     sys.types b on a.user_type_id=b.user_type_id  
 inner join 
     sys.objects c on a.object_id=c.object_id and c.Type='U' 
 left join 
     syscomments d on a.default_object_id=d.ID  
 left join 
     sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1   
 left join 
     sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1

 在 MSSQL 2008 或以上版本，可以使用以下语句 让相应用户组/用户 查看 或者 不能查看 所有数据库。

默认情况下，每个连接到 SQL Server 实例的用户都可查看该实例中的所有数据库。

DENY VIEW any DATABASE TO PUBLIC;
GRANT VIEW ANY DATABASE TO PUBLIC;

 以下SQL 语句用于删除用户时提示 不能删除发布，不能修改数据库所有者，不能删除用户 时的解决方法

ALTER AUTHORIZATION ON SCHEMA::DB_OWNER TO dbo;

 以下SQL 语句用于向指定用户展示指定数据库

ALTER AUTHORIZATION ON DATABASE::master TO dbo; 

 

除了 HOST_NAME() ，还可以使用以下语句查询IP地址：

SELECT SERVERNAME = CONVERT(NVARCHAR(128),SERVERPROPERTY('SERVERNAME')) 
,LOCAL_NET_ADDRESS AS 'IPAddressOfSQLServer'
,CLIENT_NET_ADDRESS AS 'ClientIPAddress'
 FROM SYS.DM_EXEC_CONNECTIONS WHERE SESSION_ID = @@SPID

 use master

go

grant VIEW SERVER STATE to user

 

一次添加用户后没权限，提示“sqlserver 服务器主体 无法在当前安全上下文下访问数据库。”

解决方法：

USE [TABLE_NAME]
GO
EXEC sp_addrolemember N'db_owner', N'adduser2'
GO

ALTER AUTHORIZATION ON DATABASE::TABLE_NAME TO adduser2

参考：http://www.cnblogs.com/chendaoyin/archive/2013/12/23/3487182.html