使用 jQuery 显示 SQL 表中的列标题和行

发布于 2024-12-10 15:19:25 字数 1171 浏览 1 评论 0原文

我正在尝试使用 jQuery 显示 SQL Server 表中的列和行

我使用 SELECT * FROM table1 选择表，所以我不知道列标题是什么，除非我从 中单独获取它们>sys.columns。 select 语句的输出也以 XML 格式出现，以对抗 XSS。

我如何选择列

SELECT c.column_id, c.name, y.system_type_id, y.name AS system_type_name
FROM sys.columns AS c
JOIN sys.types AS y ON c.system_type_id = y.system_type_id
JOIN sys.tables AS t ON c.object_id = t.object_id
WHERE y.name NOT LIKE 'sysname' 
AND t.object_id = '1234567890'
FOR XML RAW, TYPE, ROOT('rows');

示例 XML 输出

<rows>
  <row column_id="1" name="id" system_type_id="56" system_type_name="int" />
  <row column_id="5" name="added" system_type_id="61" system_type_name="datetime" />
  <row column_id="4" name="allowlogin" system_type_id="104" system_type_name="bit" />
  <row column_id="3" name="pw" system_type_id="165" system_type_name="varbinary" />
  <row column_id="2" name="un" system_type_id="231" system_type_name="nvarchar" />
</rows>

如何选择行

SELECT * FROM table1 FOR XML RAW, TYPE, ROOT('rows')

我不知道（可能是因为已经晚了）如何执行此操作。有什么想法吗？

原文

I am trying to display columns and rows from a SQL Server table using jQuery

I select the tables with SELECT * FROM table1 so I don't know what the column headings are unless I get them separately from sys.columns. The output from the select statement also comes out in XML format to combat XSS.

How I select columns

SELECT c.column_id, c.name, y.system_type_id, y.name AS system_type_name
FROM sys.columns AS c
JOIN sys.types AS y ON c.system_type_id = y.system_type_id
JOIN sys.tables AS t ON c.object_id = t.object_id
WHERE y.name NOT LIKE 'sysname' 
AND t.object_id = '1234567890'
FOR XML RAW, TYPE, ROOT('rows');

A sample XML output

<rows>
  <row column_id="1" name="id" system_type_id="56" system_type_name="int" />
  <row column_id="5" name="added" system_type_id="61" system_type_name="datetime" />
  <row column_id="4" name="allowlogin" system_type_id="104" system_type_name="bit" />
  <row column_id="3" name="pw" system_type_id="165" system_type_name="varbinary" />
  <row column_id="2" name="un" system_type_id="231" system_type_name="nvarchar" />
</rows>

How I select rows

SELECT * FROM table1 FOR XML RAW, TYPE, ROOT('rows')

I can't figure out (probably because its late) how to do this. Any ideas?

分享到QQ

分享到微博

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

发布评论

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

酒绊 2024-12-17 15:19:25

如果我理解正确，您希望列名位于单个字符串中，如下所示：field1，field2，field3 ...

如果是这种情况，您可以这样做：

SELECT STUFF((SELECT ','+name FROM YourDatabaseName.sys.columns WHERE object_id = OBJECT_ID('YourTableName') ORDER BY column_id FOR XML PATH('')),1,1,'')

希望有帮助！

If I understand you correctly, you want the column names in a single string like this: field1,field2,field3...

If that is the case you could do this:

SELECT STUFF((SELECT ','+name FROM YourDatabaseName.sys.columns WHERE object_id = OBJECT_ID('YourTableName') ORDER BY column_id FOR XML PATH('')),1,1,'')

Hope that helps!

回复收藏 0 原文

~没有更多了~