当前位置：首页 > 实用文档 > 其他范文> SQLSERVER语句返回表结构

SQLSERVER语句返回表结构

时间：2022-10-31 07:52:55 其他范文收藏本文下载本文

SQLSERVER语句返回表结构（精选7篇）由网友“二二狗狗”投稿提供，以下是小编为大家汇总后的SQLSERVER语句返回表结构，欢迎参阅，希望可以帮助到有需要的朋友。

SQLSERVER语句返回表结构

篇1：SQLSERVER语句返回表结构

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 + ',',

'@v_' + a.name + ' ' + b.name + cast(a.length as varchar(3)) + ',',

'',

标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,

主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id 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

a.xusertype=b.xusertype

inner join

sysobjects d

a.id=d.id and d.xtype='U' and d.name'dtproperties'

left join

syscomments e

a.cdefault=e.id

left join

sys.extended_properties g

a.id=G.major_id and a.colid=g.minor_id

left join

sys.extended_properties f

d.id=f.major_id and f.minor_id=0

where

d.name='cs_oper_information' --如果只查询指定表,加上此条件

and a.isnullable = '1'

order by

a.id,a.colorder

篇2：:sptype返回表结构字段类型及长度等

[sql]

USE [master]

IF OBJECT_ID('sp_type','P') IS NOT NULL

DROP PROCEDURE [sp_type]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

/************** object: sp_type script_datetime: -05-07 14:34:00****************/

CREATE PROC sp_type (@tab NVARCHAR(128))

作者：陈恩辉-弘恩

说明：可以简单返回表的数据类型，对数据长度精度未做详细处理

IF object_id(@tab, 'U') IS NULL

BEGIN

PRINT '在数据库：'+ db_name + ' 中，表：' + @tab + ' 不存在！'

RETURN

END

DECLARE @sqlstr VARCHAR(MAX) ;

SET @sqlstr = ' SELECT '

+ STUFF(( SELECT ',' + name + ' = ''' + type_name + ''''

FROM ( SELECT name ,

TYPE_NAME(system_type_id)

+ CASE WHEN TYPE_NAME(system_type_id) LIKE '%char'

THEN '('

+ CAST(CASE WHEN max_length < 0

THEN 'max'

ELSE max_length

END AS VARCHAR) + ')'

篇3：实际工作中常用统计SQLSERVER表大小

--返回当前数据库中所有用户表的使用空间列表

CREATE PROC [dbo].[tablespace_list]

SET NOCOUNT ON

DECLARE @tblcount INT --表的个数

,@tblname VARCHAR(128) --表名

,@schemaname varchar(64)

--用于存放最终结果的表变量

DECLARE @result TABLE (表名 VARCHAR(128),

行数 VARCHAR(11),

预申请空间 VARCHAR(15) NULL,

数据占用空间 VARCHAR(15) NULL,

索引占用空间 VARCHAR(15) NULL,

空闲 VARCHAR(15) NULL

);

DECLARE @resultEx TABLE (

架构名 varchar(64),

表名 VARCHAR(128),

行数 VARCHAR(11),

预申请空间 VARCHAR(15) NULL,

数据占用空间 VARCHAR(15) NULL,

索引占用空间 VARCHAR(15) NULL,

空闲 VARCHAR(15) NULL

);

--用于存放所有表名的表变量

DECLARE @tbl TABLE (tblname VARCHAR(128),

schemaname varchar(64),

id INT IDENTITY(1,1) NOT NULL

);

--将当前库中所有的表名存入表变量@tbl

INSERT INTO @tbl

select a.name,b.name

FROM sys.all_objects a join sys.schemas b

ON a.schema_id=b.schema_id

WHERE a.type in ('U','S');

--获得当前库中表的个数

SELECT @tblcount=MAX(id) FROM @tbl

WHILE @tblcount>0

BEGIN

SELECT @tblname=quotename(schemaname)+'.'+quotename(tblname),@schemaname=schemaname FROM @tbl WHERE id=@tblcount

INSERT INTO @result EXEC ('EXEC SP_SPACEUSED ' +'“'+@tblname+'”')

insert into @resultEx select @schemaname,* from @result

delete from @result

SET @tblcount=@tblcount-1

END

select * from @resultEx ORDER BY CONVERT(bigint,REPLACE(预申请空间,'kb','')) DESC

select 架构名,sum(CONVERT(bigint,REPLACE(预申请空间,'kb','')) ) as '总耗费空间kb'

from @resultEx

group by 架构名

order by '总耗费空间kb' desc

select sum(CONVERT(bigint,REPLACE(预申请空间,'kb','')) ) as '总耗费空间kb' from @resultEx

SET NOCOUNT OFF

篇4：SQLServer系统表的研究论文

SQLServer系统表的研究论文

摘要：本文介绍了MicrosoftSQLServer数据库的系统表，并对系统中经常遇到的一个问题给出了通过系统表建立存储过程的解决办法。

关键词：SQLServer系统表存储过程

一、SQLServer的系统表。

Microsoft的SQLServer是一个可伸缩的高性能数据库管理系统，专为分布式客户机/服务器环境而设计，SQLServer几乎将所有的配置信息、安全性信息和对象信息都存储在了它自身的系统表中，而系统表存在于每个独立的数据库中，存储一个特定数据库对象信息的系统表通常称为数据库目录，MASTER数据库有其特有的系统表用于保存整个系统和所有数据库的信息，通常称为服务器目录或系统目录。

服务器上所有的数据库包括MODULE，MASTER等都含有18个具有相同名称、结构的系统表，如表SYSOBJECTS用于描述数据库中的对象棗表、视图、存储过程等，表SYSUSER用于描述数据库的用户，而MASTER数据库另外还有13个单独的全局系统表，如表SYSLOGINS用于保存每个服务器的登录名、口令和配置信息、表SYSDATABASE保存服务器上所有数据库名、所有者、状态及其他信息。

存储过程是内嵌于数据库中的程序代码，它与表、视图等一样是数据库的一个组成部分，不同于一般的外部程序代码，它是经过预编译处理的代码，因此具有运行速度快、效率高的特点，存储过程也是SQLServer的一个重要功能，许多单纯针对后台数据库的操作一般都交由存储过程来完成以提高系统效率。本文将给出一个存储过程来说明SQLServer系统表的应用。

二、SQLServer系统表的应用。

在应用SQLServer的基于客户机/服务器体系结构的信息系统开发中，有时需要将后台SQLServer上的某一数据库的表结构都打印出来，以便于开发人员查阅及最终文档的形成。SQLServer本身提供了一个系统存储过程(SP_COLUMNS)，可以完成对单个表结构的查询，只要在SLQServer的ISQL-W工具中键入SP_COLUMNS“表名”，并执行即可得到结果集。但该方法有许多不足之处，其主要缺点是：

1、只能对数据库中单个数据表进行操作，当需要查询一个数据库中所有的表时，需要多次执行系统存储过程SP_COLUMNS，因此显得非常繁琐。

2、查询结果集中包含了许多不必要的信息，缺乏使用的灵活性。

下面我们创建一个存储过程来完成对某一个数据库中所有表结构的查询。

在创建一个数据库的同时，系统会自动建立一些系统表，限于篇幅的缘故我们在这里只介绍与应用实例有关的三个系统表(SYSOBJECTS，SYSCOLUMNS，SYSTYPES)及其相关的字段。ケ鞸YSOBJECTS为数据库内创建的每个对象(约束，规则，表，视图，触发器等)创建一条记录。

该表相关字段的含义如下：

（1）SYSOBJECTS.name对象名，如：表名，视图名。

（2）SYSONJECTS.id对象id。

（3）SYSOBJECTS.type对象类型(p存储过程，v视图，s系统表，u用户表)。

（4）表SYSCOLUMNS为每个表、视图中的每个列和每个存储过程的每个参数创建一条记录。如下：(此处的列系指数据库中每个表、视图中的列)。

①SYSCOLUMNS.id该列所属的表的id，可与SYSOBJECTS.id相关联。

②SYSCOLUMNS.colid列id，表示该列是表或视图的第几列。

③SYSCOLUMNS.type物理存储类型，可与SYSTYPES.type相关联。

④SYSCOLUMNS.length数据的物理长度。

⑤SYSCOLUMNS.name列名字，即字段名。

⑥SYSCOLUMNS.Pre列的.精度级。

⑦SYSCOLUMNS.Scale列的标度级。

⑧表SYSTYPES为每个系统和每个用户提供的数据类型创建一条记录，如果它们存在，给定域和默认值，描述系统提供的数据类型的行不可更改。

（5）该表相关字段的含义如下：

①SYSTYPES.name数据类型的名字。

②SYSTYPES.type物理存储数据类型。

③在SQLSERVER的企业管理器(SQLENTERPRISEMANAGER)中，选定某一数据库，创建存储过程print_dbstructure。

首先判断是否存在一个名为print_dbstructure的存储过程，如果存在，就摘除它，否则，定义SQL语句建立新的存储过程。从三个系统表中选出满足条件的记录(即该数据库中保存在系统表中的用户表信息)。

执行时，在ISQL_W工具中，选定print_dbstructure所在的数据库，执行该存储过程，即可得到结果集(即该数据库中用户表的结构信息)。

三、SQLServer系统表的应用推广。

以上所介绍存储过程的主要缺陷在于只能对服务器上某一特定的数据库(print_dbstructure所在的数据库)进行查询操作，我们可以通过在SQLServer系统数据库MASTER中建立带有数据库名参数的系统存储过程来解决这一问题，请读者自行完成。当然，SQLServer系统表的应用远不止于止，读者也可依本文所介绍的方法去挖掘其他几个系统表的应用。