-- 表结构查询SELECT 表名 =casewhen a.colorder=1then d.name else‘‘end, 表说明 =casewhen a.colorder=1thenisnull(f.value,‘‘) else‘‘end, 字段序号 = a.colorder, 字段名 = a.name, 标识 =casewhenCOLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1then‘√‘else‘‘end, 主键 =casewhenexists(SELECT1FROM sysobjects where xtype=‘PK‘and parent_obj=a.id and name in ( SELE...
ORACLE：
--时间转字符串
TO_CHAR(SYSDATE,‘YYYYMMDDHH24MISS‘)
--字符串转时间
select TO_DATE(‘20140701125751‘,‘YYYYMMDDHH24MISS‘) from dual -- 2014-07-01 12:57:51
--查询table
SELECT * FROM ALL_TABLES WHERE TABLE_NAME =‘{0}‘
--查询column
SELECT
aa.table_name AS tableename,
aa.column_name AS colname,
NVL (bb.comments, aa.column_name) AS colde...
select a.name,
b.name + ‘(‘+ convert(varchar,a.length) + ‘)‘ + case a.isnullable when 1 then ‘ Null‘ else ‘‘ end as [type],
‘‘ as Pretty
from syscolumns a join systypes b
on (a.xusertype = b.xusertype)
where a.id = object_id(‘TableName‘) Order By a.ColOrder得到结果为：获取SQL SERVER数据库表结构标签：表结构 数据库 sql server本文系统来源：http://2311390.blog.51cto.com/2301390/1702449
查询表结构start
SELECT 序号 = a.colorder,字段名称 = a.name,字段描述 = f.value,
标识 = 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 ‘√...
SELECT (case when a.colorder=1 then d.name else null end) 表名, a.colorder 字段序号,a.name 字段名, (case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end) 标识, (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT ...
1 SELECT (case when a.colorder=1 then d.name else null end) 表名, 2 a.colorder 字段序号,a.name 字段名,3 (case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end) 标识, 4 (case when (SELECT count(*) FROM sysobjects 5 WHERE (name in (SELECT name FROM sysindexes 6 WHERE (id = a.id) AND (indid in 7 (SELECT indid FROM sysindexkeys 8 WHERE (id = a.id) AND (colid in 9 (SELE...
CREATE TABLE [dbo].[Sys_CreateTable]([序号] [int] IDENTITY(1,1) NOT NULL,[中文名] [nvarchar](500) NULL,[英文名] [nvarchar](500) NULL,[类型、宽度、精度] [nvarchar](500) NULL,[默认值/描述] [nvarchar](500) NULL,[表名] [nvarchar](500) NULL,[表中文名] [nvarchar](500) NULL,
CONSTRAINT [PK_Sys_CreateTable] PRIMARY KEY CLUSTERED
([序号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_D...
SELECT (case when a.colorder=1 then d.name else null end) 表名, 2 a.colorder 字段序号,a.name 字段名,3 (case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then ‘√‘else ‘‘ end) 标识, 4 (case when (SELECT count(*) FROM sysobjects 5 WHERE (name in (SELECT name FROM sysindexes 6 WHERE (id = a.id) AND (indid in 7 (SELECT indid FROM sysindexkeys 8 WHERE (id = a.id) AND (colid in 9 (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,字段说明 = isnull(G.[value],‘‘),标识 = 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 n...
对于SQL Server数据库有两种方法查询表结构
第一种方法
sp_help Accounts_Users 其中Accounts_Users 表示表名
执行结果如下
第二种方法
sp_columns Accounts_Users
执行结果如下 SQL Server里查询表结构命令标签：技术分享 accounts 9.png com 查询 nbsp use png ima 本文系统来源：http://www.cnblogs.com/wangyuebo/p/6265960.html
PROC [sp_tableHelp] (@TableName VARCHAR(128) = ‘‘ ,@ColName VARCHAR(128)=‘‘)
AS
BEGIN--表结构速查--Brin--DECLARE @TableName VARCHAR(128)--DECLARE @ColName VARCHAR(128)--SET @TableName = ‘tLocatorMove‘--SET @ColName = ‘nStatus‘SELECTtableName = (CASE WHEN col.colorder = 1 OR @TableName IS NULL THEN obj.name ELSE ‘‘ END ),colOrder = col.colorder,colName = col.name,colDescription = ISNULL...
最近修改了数据库表结构，数据同步的时候出了问题，发现很多数据明明已经修改，但是通过视图筛选出来的还是原来的数据，所以怀疑应该是视图缓存了数据，在园子里找到下面的博文，在这里做个记录备忘。
原文链接：http://www.cnblogs.com/yashen/archive/2004/12/23/81000.html
我们在使用SqlServer时经常遇到这种情况，当修改某个表的结构后，相关的视图就不对了而导致程序错误，因此就有个下面这个存储过程。CREATE PROCEDURE...
SQL Server2008中通过SQL获取表结构新增数据用户，角色为public。映射到待获取表结构的数据库上，授与用户在该数据库上的身份为db_owner运行例如以下SQL语句：select
syscolumns.name as [Name] ,
systypes.name as [Type],
syscolumns.length AS [Size],
syscolumns.xprec As [Precision],
ISNULL(syscolumns.scale, 0) AS [Scale],
sys.extended_properties.value as [Mark],
syscolumns.isnullable as [NULL],
(Case when exi...
USE database_name
GOSELECT tab.name AS tableName
FROM sys.columns col
INNER JOIN sys.tables tab
ON col.object_id = tab.object_id
LEFT JOIN sys.extended_properties per
ON col.column_id = per.minor_id
AND per.major_id = tab.object_id
INNER JOIN sys.types type
ON col.user_type_id = type.user_type_id
group by ta...
dependency><groupId>com.microsoft.sqlserver</groupId><artifactId>mssql-jdbc</artifactId><version>6.1.0.jre8</version>
</dependency>2、建立测试的Job，我建立的是一个简单的restful接口调用sql server存储过程的示例，如下：在该示例中：
tLibraryLoad：主要是把我们第一步下载的jar加载进来；
tRESTRequest：输出body设置为Document；
tJavaRow：自己写代码调用带表结构list参数的存储过程，Base Settings code如下...
查询表结构start
SELECT 序号 = a.colorder,字段名称 = a.name,字段描述 = f.value,
标识 = 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,
长度 = COLUMNPROPERTY(a.id,a.name,‘PRECISION‘),
允许空 = case when a.isnullable = 1 then ‘√‘ else ‘‘ end,
默认值 = isnull(e.text,‘‘)
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 sys.extended_properties f on f.major_id = d.id and f.minor_id = a.colorder
where d.name=‘表名‘
order by a.id,a.colorder
--查询表结构end