数据库表和列信息

最新推荐文章于 2024-11-12 20:35:48 发布

weixin_30335575

最新推荐文章于 2024-11-12 20:35:48 发布

阅读量87

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/wang2650/articles/1663512.html

版权

数据库的表信息

select [name] from sysobjects where xtype='U'and [name]<>'dtproperties' order by [name]

数据库表的列信息
sp_columns 'wxq_permissioninfo'

某列的说明字段的信息
select (select value from sys.extended_properties where name='MS_Description' and major_id=id and minor_id=colorder) from syscolumns where id=object_id('wxq_permissioninfo') and name='PositionId'

获取表的扩展属性

SELECT p.* FROM sys.tables AS tbl INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1 WHERE (tbl.name=N'表名' and SCHEMA_NAME(tbl.schema_id)=N'dbo') AND p.name='属性名'

获取某个表字段的扩展属性

SELECT *
FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '表名', 'column', default)

/*******************************数据库扩展属性*************************************/
--1、如果有扩展属性（user_key）
IF EXISTS(SELECT name FROM sys.extended_properties WHERE major_id=0 AND minor_id=0 AND class=0 AND name='user_key')
--2、删除扩展属性（user_key）
EXEC [Constructor].sys.sp_dropextendedproperty @name=N'user_key'
--3、新增扩展属性（user_key）
EXEC [Constructor].sys.sp_addextendedproperty @name=N'user_key', @value=N'DF3E-T66YH-K934U-22TTF'
--4、查看扩展属性（user_key）
SELECT * FROM sys.extended_properties WHERE major_id=0 AND minor_id=0 AND class=0 AND name='user_key'

/*******************************表的扩展属性（ListInfo清单表为例）*************************************/
--1、如果有扩展属性（user_key）
IF EXISTS(SELECT p.* FROM sys.tables AS tbl INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1 WHERE (tbl.name=N'ListInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo') AND p.name='user_key')
--2、删除扩展属性（user_key）
EXEC sys.sp_dropextendedproperty @name=N'user_key', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ListInfo'
--3、新增扩展属性（user_key）
EXEC sys.sp_addextendedproperty @name=N'user_key', @value=N'abcd' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ListInfo'
--4、查看扩展属性（user_key）
SELECT p.* FROM sys.tables AS tbl INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1 WHERE (tbl.name=N'ListInfo' and SCHEMA_NAME(tbl.schema_id)=N'dbo') AND p.name='user_key'

varchar=string
varchar2=string
nvarchar=string
nvarchar2=string
char=string
nchar=string
text=string
longtext=string
ntext=string
string=string
date=DateTime
datetime=DateTime
smalldatetime=DateTime
smallint=int
single=Single
int=int
number=int
bigint=long
tinyint=int
float=decimal
numeric=decimal
decimal=decimal
money=decimal
smallmoney=decimal
real=decimal
bit=bool
binary=byte[]
varbinary=byte[]
image=byte[]
raw=byte[]
long=byte[]
long raw=byte[]
blob=byte[]
bfile=byte[]
uniqueidentifier=Guid
integer=int
double=Double
enum=Enum
timestamp=DateTime
variant=object