试图在SQL中调用特定信息进行显示 [英] Trying to call forth specific information in SQL to be displayed

问题描述

我找到了一个公共代码，它接受表并显示它们以及记录（行）的数量。我使用了该代码并尝试添加更多代码以允许从每个表填充更多字段。问题是我不能只使用一般的列名来继续问我具体要从哪个表中提取。我想从所有表格中获取一个通用列，即出生年份，并将Listview填充为第1年为最小年份，第2年为这些表格中日期的最大年份。我还希望能够使用listview表中的sys.database来显示表一次查看所有表时与哪个数据库相关联的数据库。



所以对于listview到目前为止，我能够看到表名和最大记录数。它应该是这样的：



|表名|记录| Year1 | YEAR2 |数据库|





此代码不完整，因为我很难找到各个列名并使用sys.database这是我正在处理的代码：

 SELECT 
 [TableName] = so.name，
 [RowCount ] = MAX（si.rows），
 [数据库] = SCH 
 [最小] = MIN（CMI。），
 [最大] = MAX（CMA.number）
 FROM 
 sysobjects so，
 sysindexes si，
 sys.databases SCM，
 sys.all_columns CMI，
 syscolumns CMA 
 WHERE 
 so。 xtype ='U'
 AND 
 si.id = OBJECT_ID（so.name）
和
 CMA。 
和
 CMI。 
和
 SCM 
 GROUP BY 
 so.name 
 ORDER BY 
 5 DESC 

我已经放置了一些我认为正朝着正在努力完成的方向发展的东西。



我希望能够获得特定列的最小值和最大值，并且我还希望显示这些表所属的数据库。最后我希望能够显示我上面列出的所有数据库组合信息。



这里是原始的：

 SELECT 
 [TableName] = so.name，
 [RowCount] = MAX（si.rows）
 FROM 
 sysobjects so，
 sysindexes si 
 WHERE 
 so.xtype ='U'
 AND 
 si.id = OBJECT_ID（so.name）
 GROUP BY 
 so.name 
 ORDER BY 
 2 DESC 

无论如何我试图通过一些额外的字段来获得这个，我将通过listview传递值作为sqldatareader，这将使它成为唯一的只读视图。我可以使用原件，它工作正常，因为它只显示实际的表名和最大行数。



我尝试过：



我试图将属性从sys.columns切换到所有列，我试图只设置名称本身CMA.name，CMI.name和SCM.name



https://gyazo.com/f53f611cc9a9f6f2fd28c01de0e78ec9



我已经拍了一张照片

解决方案

好的。我仍然认为你的设置很奇怪，我们很难理解你想要做什么，但是这里有一些你可以根据自己的要求改变的东西。



我正在创建一个中期结果表：

 如果 存在（选择 * 来自 INFORMATION_SCHEMA.TABLES 
  where  TABLE_NAME = '  tempResults'  AND  TABLE_SCHEMA = '  dbo'） drop   table  dbo.tempResults 
 
  create   table  tempResults 
（
 id  int   identity （ 1 ， 1 ），
 databasename  varchar （ 128 ），
 schemaname  varchar （ 128 ），
 tablename  varchar （ 128 ）， 
 columnname  varchar （ 1128 ），
 rowsintable  int ，
 system_type_id  int ，
 minvalue  varchar  （ 128 ），
 maxvalue  varchar （ 128 ）
）

注意 minvalue 和 maxvalue 列不能从系统表中确定（这是你的方法问题的一部分）



我填充了表initi与我服务器上每个数据库的每个表的每一列结盟：

  DECLARE   @src   NVARCHAR （MAX）， @sql   NVARCHAR < /跨度>（MAX）; 
 
  SELECT   @ sql  = N ' '， @ src  = N  '  UNION ALL 
 SELECT''

d''作为数据库名，
s.name COLLATE SQL_Latin1_General_CP1_CI_AI as schemaname，
t.name COLLATE SQL_Latin1_General_CP1_CI_AI as tablename，
c.name COLLATE SQL_Latin1_General_CP1_CI_AI as columnname，
i.rowcnt as rowsintable，c.system_type_id as system_type_id，
cast（0 as varchar（128））as minvalue，cast（0 as varchar（128））as maxvalue
FROM

d] .sys.schemas AS s
INNER JOIN

I found a public code that takes the tables and displays them along with the amount of records(rows). I used that code and tried to add more to it to allow more fields to be populated from each table. The problem is I cannot get just a general column name to use it keeps asking me specifically which table do I want to pull from. I want to take a general column which is the year of birth from all tables and populate the Listview as year 1 being the min year and year 2 being the max year of dates within those tables. I also want to be able to use the sys.database in the listview table to show which database the table is associated with when they view all tables at once.

so for listview as of now, I am able to see the table names and the max amount of records. this is how it should look:

| table name | records | Year1 | Year2| Database|


this code is not complete as I am having a hard time getting to the individual column names and also using sys.database here is the code I am working on:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows), 
	[databases] = SCH
	[Minimum] = MIN(CMI.),
	[Maximum] = MAX(CMA.number)
	FROM 
    sysobjects so, 
    sysindexes si, 
	sys.databases SCM,
	sys.all_columns CMI,
	syscolumns CMA
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name)
	and
	CMA.
	and
	CMI.
	and 
	SCM
GROUP BY 
    so.name 
ORDER BY 
    5 DESC

I have placed in a portion of what I believe to be in the right direction of what I am trying to accomplish.

I want to be able to get the min and max of a particular column and I want to also display which database these tables belong to. in the end I am hoping to show all my databases combined information as listed above.

here is the original:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

anyways I am trying to get this with a few extra fields, I will be passing the values through a listview as an sqldatareader, which will make it an only read only view. I can use the original and it works fine as it only shows the actual table name and the max row count.

What I have tried:

I have tried to switch the properties from sys.columns to all columns and I have tried to set just the name itself CMA.name, CMI.name, and SCM.name

https://gyazo.com/f53f611cc9a9f6f2fd28c01de0e78ec9

I have inclded a picture

解决方案

Ok. I still think your set up is strange and we're struggling to understand what you are trying to do, however here is some stuff you can alter to your own requirements.

I'm creating a interim results table:

if exists (select * from INFORMATION_SCHEMA.TABLES 
where TABLE_NAME = 'tempResults' AND TABLE_SCHEMA = 'dbo') drop table dbo.tempResults

create table tempResults
(
	id int identity(1,1),
	databasename varchar(128),
	schemaname varchar(128),
	tablename varchar(128),
	columnname varchar(1128),
	rowsintable int,
	system_type_id int,
	minvalue varchar(128),
	maxvalue varchar(128)
)

Note the minvalue and maxvalue columns cannot be determined from the system tables (which is part of the problem with your approach)

I've populated that table initially with every column of every table of every database on my server :

DECLARE @src NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @sql = N'', @src = N' UNION ALL 
SELECT ''

d'' as databasename, s.name COLLATE SQL_Latin1_General_CP1_CI_AI as schemaname, t.name COLLATE SQL_Latin1_General_CP1_CI_AI as tablename, c.name COLLATE SQL_Latin1_General_CP1_CI_AI as columnname, i.rowcnt as rowsintable, c.system_type_id as system_type_id, cast(0 as varchar(128)) as minvalue, cast(0 as varchar(128)) as maxvalue FROM

d].sys.schemas AS s INNER JOIN

这篇关于试图在SQL中调用特定信息进行显示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！