直接上代码吧
use pubs
go
CREATE TABLE [test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[A] [bigint] NOT NULL ,
[B] [binary] (33) NOT NULL ,
[C] [bit] NULL CONSTRAINT [DF_test_C] DEFAULT (0),
[D] [char] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_test_D] DEFAULT ('1'),
[E] [datetime] NULL CONSTRAINT [DF_test_E] DEFAULT (getdate()),
[F] [decimal](18, 0) NULL ,
[G] [float] NULL ,
[H] [image] NULL ,
[I] [int] NULL ,
[J] [money] NULL ,
[K] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[L] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[M] [numeric](18, 1) NULL ,
[N] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[O] [real] NULL ,
[P] [smalldatetime] NULL ,
[Q] [smallint] NULL ,
[R] [smallmoney] NULL ,
[S] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[T] [sql_variant] NULL ,
[U] [timestamp] NULL ,
[V] [tinyint] NULL ,
[W] [uniqueidentifier] NULL ,
[X] [varbinary] (50) NULL ,
[Y] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Z] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID],
[A],
[B]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOuse master
go
SELECT COLUMNPROPERTY(
OBJECT_ID('pubs..test'),COLUMN_NAME,'IsIdentity')
,COLUMN_NAME
from pubs.INFORMATION_SCHEMA.columns WHERE TABLE_NAME='test'返回的结果:ID这一列是NULL,这是为什么?除了Use Pubs外,还有别的解决办法吗?我的目的只是要在master中读取pubs..test表的种子列
use pubs
go
CREATE TABLE [test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[A] [bigint] NOT NULL ,
[B] [binary] (33) NOT NULL ,
[C] [bit] NULL CONSTRAINT [DF_test_C] DEFAULT (0),
[D] [char] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_test_D] DEFAULT ('1'),
[E] [datetime] NULL CONSTRAINT [DF_test_E] DEFAULT (getdate()),
[F] [decimal](18, 0) NULL ,
[G] [float] NULL ,
[H] [image] NULL ,
[I] [int] NULL ,
[J] [money] NULL ,
[K] [nchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[L] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[M] [numeric](18, 1) NULL ,
[N] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[O] [real] NULL ,
[P] [smalldatetime] NULL ,
[Q] [smallint] NULL ,
[R] [smallmoney] NULL ,
[S] [text] COLLATE Chinese_PRC_CI_AS NULL ,
[T] [sql_variant] NULL ,
[U] [timestamp] NULL ,
[V] [tinyint] NULL ,
[W] [uniqueidentifier] NULL ,
[X] [varbinary] (50) NULL ,
[Y] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Z] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[ID],
[A],
[B]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOuse master
go
SELECT COLUMNPROPERTY(
OBJECT_ID('pubs..test'),COLUMN_NAME,'IsIdentity')
,COLUMN_NAME
from pubs.INFORMATION_SCHEMA.columns WHERE TABLE_NAME='test'返回的结果:ID这一列是NULL,这是为什么?除了Use Pubs外,还有别的解决办法吗?我的目的只是要在master中读取pubs..test表的种子列
----------- --------------------------------------------------------------------------------------------------------------------------------
NULL ID
NULL A
NULL B
NULL C
NULL D
NULL E
NULL F
NULL G
NULL H
NULL I
NULL J
NULL K
NULL L
NULL M
NULL N
NULL O
NULL P
NULL Q
NULL R
NULL S
NULL T
NULL U
NULL V
NULL W
NULL X
NULL Y
NULL Z(所影响的行数为 27 行)
--------------------------------------- --------------------------------------- ---------------------------------------
1 1 1(1 行受影响)
go
SELECT COLUMNPROPERTY(
OBJECT_ID('pubs..test'),COLUMN_NAME,'IsIdentity')
,COLUMN_NAME
from pubs.INFORMATION_SCHEMA.columns WHERE ID=OBJECT_ID('pubs..test')
go
SELECT COLUMNPROPERTY(
OBJECT_ID('pubs..test'),NAME,'IsIdentity')
,NAME
from pubs..SYScolumns WHERE ID=OBJECT_ID('pubs..test')
goSELECT
COLUMN_NAME
from INFORMATION_SCHEMA.columns WHERE TABLE_NAME='test'
and COLUMNPROPERTY(OBJECT_ID('test'),COLUMN_NAME,'IsIdentity')=1
COLUMN_NAME
----------------------------------------------------------------------------------------------------
Id(1 行受影响)
goSELECT
COLUMN_NAME
from INFORMATION_SCHEMA.columns WHERE TABLE_NAME='test'
and COLUMNPROPERTY(OBJECT_ID('test'),COLUMN_NAME,'IsIdentity')=1
COLUMN_NAME
,
is_identity from pubs.sys.columns WHERE object_id = object_id('pubs..test')
那如果不在当前库下,怎么解决这个问题?直接从syscolumns的字段里拆分?不过那还得拆分数据啊,而且还不知道它存储的规律呢。。
对象名 'pubs.sys.columns' 无效。
from pubs..syscolumns
WHERE id = object_id('pubs..test')
and autoval is not null
SELECT name
from test..syscolumns
WHERE id = object_id('test..test')
and autoval is not null
--2005
SELECT name
from test..syscolumns
WHERE id = object_id('test..test2')
and colstat =1
SELECT name
from pubs..syscolumns
WHERE id = object_id('pubs..test')
and autoval is not null
--2005
SELECT name
from pubs..syscolumns
WHERE id = object_id('pubs..test')
and colstat =1