菜单

SQL SERVER 系统 表 操作

2020年2月29日 - 计算机数据

高效分页方法代码(sql百万级数据量分页代码)

–得到数据库中所有用户表
Select [name] from sysObjects Where xtype=’U’and
[name]<>’dtproperties’ Order By [name]
–得到数据库中所有用户视图
Select [name] From sysObjects Where xtype=’V’ And
[name]<>’syssegments’ And [name]<>’sysconstraints’ Order
By [name]
–获得指定表中所有的列
Select
c.name As ColumnName,
t.name As TypeName
From syscolumns c, systypes t, sysobjects o
Where c.xtype = t.xusertype
And c.id = o.id
And o.name = ‘Book’
Order By c.colorder
–获得表中所有列的详细信息
Select  ColOrder = col.colorder, –排序号
ColumnName = col.name, –列名
TypeName = type.name,–数据类型名称
Length =  (Case When type.name=’nvarchar’ Or type.name=’nchar’ Then
col.length/2 Else col.length End), –长度
[PRECISION] = COLUMNPROPERTY(col.id, col.name, ‘PRECISION’), –精度
Scale = ISNULL(COLUMNPROPERTY(col.id, col.name, ‘Scale’), 0), –小数
IsIdentity = Case When COLUMNPROPERTY(col.id, col.name, ‘IsIdentity’)=1
Then ‘√’ Else ” End, –是否为自动编号列
IsPK = Case When Exists(Select 1 From sysobjects Where xtype = ‘PK’ And
name In (
     Select name From sysindexes Where indid In (
      Select indid From sysindexkeys Where id = col.id And colid =
col.colid
      )
     )
    ) Then ‘√’ Else ” End, –是否为主键
AllowNull = Case When col.isnullable=1 Then ‘√’ Else ” End,
–是否允许为空
DefalutValue = isnull(com.text, ”) –默认值
From syscolumns col
Left Join systypes type On col.xtype = type.xusertype
Inner Join sysobjects obj On col.id = obj.id And (obj.xtype = ‘U’ Or
obj.xtype = ‘V’) And obj.name <> ‘dtproperties’
Left Join syscomments com On col.cdefault = com.id
Where obj.name = ‘Territories’

@querystr nvarchar(300),–表名、视图名、查询语句@pagesize
int=10,–每页的大小(行数)@pagecurrent int=1,–要显示的页@fdshow nvarchar
(100)=”,–要显示的字段列表,如果查询结果有标识字段,需要指

=============================================
获取MS SQL库数据字典的经典SQL语句

定此值,且不包含标识字段@fdorder nvarchar
(100)=”,–排序字段列表@wherestr nvarchar (200)=”, –内容是’ id=3 and
model_no like ‘%24%’

SELECT sysobjects.name AS [table], sysproperties.[value] AS
表说明,
syscolumns.name AS field, properties.[value] AS 字段说明,
systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id,
syscolumns.name,
‘Scale’), 0) AS 小数位数, syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL
THEN ” ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, ‘IsIdentity’)
= 1 THEN ‘√’ ELSE ” END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = ‘PK’ AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN ‘√’ ELSE ” END AS 主键
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id

and ‘@rscount int=0 output asset @fdshow=’ ‘+@fdshow+’ ‘set @fdorder= ‘
‘+@fdorder+’ ‘set @wherestr= ‘ ‘+@wherestr+’ ‘

WHERE (sysobjects.xtype = ‘U’)

获取数据库中表的字段的名称及类型

select   syscolumns.name,systypes.name       from      
syscolumns,systypes     where       id=object_id( ‘POSmanage..PayWays
‘)   and   systypes.xusertype=syscolumns.xusertype
给你一个通过查询系统表得到纵向的表结构的例子.完全可以满足你的要求.
SELECT 
表名=case   when   a.colorder=1   then   d.name   else   ‘ ‘   end,
表说明=case   when   a.colorder=1   then   isnull(f.value, ‘ ‘)   else  
‘ ‘   end,
字段序号=a.colorder,
字段名=a.name,
标识=case   when   COLUMNPROPERTY(   a.id,a.name, ‘IsIdentity ‘)=1  
then   ‘√ ‘else   ‘ ‘   end,
主键=case   when   exists(SELECT   1   FROM   sysobjects   where  
xtype= ‘PK ‘   and   name   in   (
SELECT   name   FROM   sysindexes   WHERE   indid   in(
SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND  
colid=a.colid
)))   then   ‘√ ‘   else   ‘ ‘   end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name, ‘PRECISION ‘),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name, ‘Scale ‘),0),
允许空=case   when   a.isnullable=1   then   ‘√ ‘else   ‘ ‘   end,
默认值=isnull(e.text, ‘ ‘),
字段说明=isnull(g.[value], ‘ ‘)
FROM   syscolumns   a
left   join   systypes   b   on   a.xusertype=b.xusertype
inner   join   sysobjects   d   on   a.id=d.id   and   d.xtype= ‘U ‘  
and   d.name <> ‘dtproperties ‘
left   join   syscomments   e   on   a.cdefault=e.id
left   join   sysproperties   g   on   a.id=g.id   and  
a.colid=g.smallid
left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0
–where   d.name= ‘shebei ‘

declare @fdname nvarchar(250)–表中的主键或表、临时表中的标识列名,@id1
varchar(20),@id2 varchar(20)–开始和结束的记录号,@obj_id int
–对象id,@temp nvarchar(300) –临时语句,@strparam nvarchar(100)
–临时参数

order   by   a.id,a.colorder

用SQL查询分析器查询表的字段类型长度和表说明

SELECT sysobjects.name AS 表名, syscolumns.name AS 列名,
systypes.name AS 数据类型, syscolumns.length AS 数据长度,
CONVERT(char,
sysproperties.[value]) AS 注释
FROM sysproperties RIGHT OUTER JOIN
sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype ON
sysproperties.id = syscolumns.id AND
sysproperties.smallid = syscolumns.colid
WHERE (sysobjects.xtype = ‘u’ OR
sysobjects.xtype = ‘v’) AND (systypes.name <> ‘sysname’)
–and CONVERT(char,sysproperties.[value]) <> ‘null’
–导出注释不为’null’的记录
–AND (sysobjects.name = ‘bbs_bank_log’)
–逐个关联表名,可以用or连接条件

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图