将文本转换为数据库对象和字段标识符

发布于 2024-11-18 05:10:58 字数 491 浏览 2 评论 0原文

给定一个列出所有表和列的 select 语句，如何将表和列名称转换为可在单独的 select 语句中使用的标识符？

换句话说，如果我有字符串 @Table = 'Person' 和 @Column = 'Name'，我想要这样的东西：

select 
    DATALENGTH(MAX(t.@Column)) as Longest, 
    DATALENGTH(MIN(t.@Column)) as Shortest 
from @Table as t;

当然，这并不能实现我想要的。我想知道存储在列中的最长和最短字符串的长度。如果我想要单个列的此信息，则无需使用这些字符串化变量。但我想对数据库中的每个 (varchar) 列执行此操作。生命太短暂，无法创建每条 SQL 语句来完成此任务。这就是为什么我需要一个参数机制来指定表和列。我该如何解决这个问题以实现目标？

当然，我的想法可能全错了。也许我应该按索引寻址每一列，并仅在需要输出时才转换为列名。想法？

原文

Given a select statement that lists all my tables and columns, how do I convert the table and column names into identifiers that can be used in a seperate select statement?

In other words, if I have the string @Table = 'Person' and @Column = 'Name', I want something like this:

select 
    DATALENGTH(MAX(t.@Column)) as Longest, 
    DATALENGTH(MIN(t.@Column)) as Shortest 
from @Table as t;

Of course that does not accomplish what I want. I want to know the length of the longest and shortest string stored in a column. If I wanted this information for a single column, there would be no need to use these stringified-variables. But I want to do this for every (varchar) column across my database. Life is too short to create every SQL statement to accomplish this. This is why I want a parametric mechanism for specifying the table and column. How do I fix this to achieve the goal?

Of course I could be going about this all wrong. Perhaps I should address each column by index, and convert to a column name only when output is needed. Thoughts?

分享到QQ

分享到微博

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

发布评论

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

此刻的回忆 2024-11-25 05:10:58

DECLARE @sql VARCHAR(999)
DECLARE @col VARCHAR(50)
DECLARE @table VARCHAR(50)

SET @col = <colname>
SET @table = <tablename>

SET @SQL = '
select 
DATALENGTH(MAX(t.@Column)) as Longest, 
DATALENGTH(MIN(t.@Column)) as Shortest 
from @Table as t'

SET @SQL = REPLACE(REPLACE(@SQL, '@Column', @col), '@Table', @table)

EXEC(@SQL)

DECLARE @sql VARCHAR(999)
DECLARE @col VARCHAR(50)
DECLARE @table VARCHAR(50)

SET @col = <colname>
SET @table = <tablename>

SET @SQL = '
select 
DATALENGTH(MAX(t.@Column)) as Longest, 
DATALENGTH(MIN(t.@Column)) as Shortest 
from @Table as t'

SET @SQL = REPLACE(REPLACE(@SQL, '@Column', @col), '@Table', @table)

EXEC(@SQL)

回复收藏 0 原文