获取表信息

发布于 2024-12-08 14:05:54 字数 538 浏览 1 评论 0原文

我试图获取表信息并给出表名称，因此我编写了如下查询：

 SELECT so.name, sc.name, st.name, sc.length, CASE WHEN sc.status = 0x80 THEN 'Y' ELSE 'N' END AS IsIdent, ColOrder
   FROM Asdim.dbo.sysobjects so
   INNER JOIN Asdim.dbo.syscolumns sc
   ON so.id=  sc.id
   INNER JOIN Asdim.dbo.systypes st
   ON sc.xtype = st.xusertype
   WHERE so.Name = 'Admin'

问题是我有两个名为“Admin”的表，但它们具有不同的架构。因此，当我运行此查询时：

SELECT * FROM Asdim.dbo.sysobjects WHERE name LIKE 'Admin'

我得到两条记录，因为表名相同。有没有一种方法可以根据架构名称进行过滤？

原文

I am trying to get the table information give a table name so I wrote a query like this:

 SELECT so.name, sc.name, st.name, sc.length, CASE WHEN sc.status = 0x80 THEN 'Y' ELSE 'N' END AS IsIdent, ColOrder
   FROM Asdim.dbo.sysobjects so
   INNER JOIN Asdim.dbo.syscolumns sc
   ON so.id=  sc.id
   INNER JOIN Asdim.dbo.systypes st
   ON sc.xtype = st.xusertype
   WHERE so.Name = 'Admin'

The problem is that I have two tables with name 'Admin' but they have different schemas. So when I run this query:

SELECT * FROM Asdim.dbo.sysobjects WHERE name LIKE 'Admin'

I get two records since the table names are same. Is there a way that I caould filter out based on the schema name too?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

错爱 2024-12-15 14:05:54

您正在使用的视图均已弃用，只是为了向后兼容而提供。使用新的视图给出。

SELECT t.name,
       c.name,
       ty.name,
       c.is_identity,
       c.max_length,
       c.column_id
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id=t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE t.name LIKE '%Admin%' AND s.name = 'dbo'

INFORMATION_SCHEMA.COLUMNS 几乎包含您需要的所有信息，但缺少有关标识列的信息。

The views you are using are all deprecated and just supplied for backward compatibility. Using the new views gives.

SELECT t.name,
       c.name,
       ty.name,
       c.is_identity,
       c.max_length,
       c.column_id
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id=t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
WHERE t.name LIKE '%Admin%' AND s.name = 'dbo'

INFORMATION_SCHEMA.COLUMNS has nearly all the information you need but is missing info about identity columns.

回复收藏 0 原文

~没有更多了~