Is there a quick way to view the size of an existing column when writing a query?

Question

I'm in the process of transferring lots of embedded SQL in some SSRS reports to functions. The process generally involves taking the current select query, adding an INSERT INTO part and returning a results table. Something like this:

CREATE FUNCTION [dbo].[MyReportFunction]
(
    @userid        varchar(255),
    @location      varchar(255),
    more params here...
)

RETURNS @Results TABLE
(
    Title        nvarchar(max),
    Location     nvarchar(255),
    more columns here...
)
AS
BEGIN
    INSERT INTO @Results (Title, Location, more columns...)
    SELECT tblA.Title, tblB.Location, more columns...
    FROM TableA tblA
    INNER JOIN TableB tblB
    ON tblA.Id = tblB.Id
    WHERE tblB.Location = @location

    RETURN

END

As part of this I have to put the columns into the @Results table and give them the correct size and type as per what's returned from the SELECT query. Now getting the type is fine as I can just copy and paste the existing SELECT query into a new query and mouse over the column name to get that e.g. column Title (nvarchar, null). However, I also need to know the size. Is there an easy way of doing that without having to go to the particular tables and looking at the column list? Is there perhaps a way of editing the tooltip and the information it displays on mouse over?

If I've got a lot of joins to different tables and a long list of tables to scroll through this can get tiresome.

I'm using SQL Server 2008 R2

Zohar Peled · Answer 1 · 2015-04-28T10:36:23.870

2

You could simply query the INFORMATION_SCHEMA.COLUMNS view:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN('TableA', 'TableB')
AND COALESCE(CHARACTER_MAXIMUM_LENGTH, -1) > 0

I've filtered the results a little further by adding the condition on CHARACTER_MAXIMUM_LENGTH to the result set.
This will give you only the columns that has maximum length (varchar, char, nvarchar etc`).
I hope this helps a little more.

edited Apr 28 '15 at 10:36

answered Apr 28 '15 at 08:55

Zohar Peled

79,642
10
69
121

This still seems a little cumbersome as some of the tables have a large number of columns and I'd need to scroll through them just to check the size. I could probably do a search for column names as well I suppose, but it's not quite the 'quick tip' I was hoping for. – sr28 Apr 28 '15 at 09:00
seems like a fine way to me – t-clausen.dk Apr 28 '15 at 10:18
@t-clausen.dk - it's quicker than perhaps scrolling to my different tables each time, but not quite what I was hoping exists out there. If nothing better comes up then I'll mark this as the best answer. – sr28 Apr 28 '15 at 10:26

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

As Zohar suggests, you can use INFORMATION_SCHEMA.COLUMNS, and then let the query engine do the heavy lifting for you

SELECT TOP 0 * INTO #Temp FROM ( OriginalQuery ) o
SELECT column_name+' ' + 
        data_type + 
        case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') 
        end +
        case when exists ( 
            select id from syscolumns
            where object_name(id)=TABLE_NAME
            and name=column_name
            and columnproperty(id,name,'IsIdentity') = 1 
            ) then
            ' IDENTITY(' + 
            cast(ident_seed(TABLE_NAME) as varchar) + ',' + 
            cast(ident_incr(TABLE_NAME) as varchar) + ')'
            else ''
        end + 
         (case when IS_NULLABLE = 'No' then ' NOT ' else ' ' end ) + 'NULL' + 
          case when tempdb.INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT IS NOT NULL THEN ' DEFAULT '+ tempdb.information_schema.columns.COLUMN_DEFAULT ELSE '' END + ',' 
FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#Temp%'
DROP TABLE #TEMP

edited May 23 '17 at 12:14

Community

1
1

answered Apr 28 '15 at 10:47

Caleth

52,200
2
44
75

very nice! I wish I would have thought about it myself :-) – Zohar Peled Apr 28 '15 at 10:51
possible improvement: construct a column definition rather than just dump the COLUMNS view, eg taking from http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table – Caleth Apr 28 '15 at 10:54
Now all you have to do is pack it inside a stored procedure or a function that will receive the original query as a parameter and return the columns definitions ready to use and you're done. I would vote this up again if I could. – Zohar Peled Apr 28 '15 at 11:18
Interesting. I will test this in a bit. Thanks. – sr28 Apr 28 '15 at 12:03
I'm a little confused by this. How do I insert my current query? Sorry for being stupid but I've tried something basic like SELECT TOP 0, JobNumber FROM MyTable INTO #Temp FROM Tables but I get an incorrect syntax error near the first comma and the 'INTO'. I've tried jigging this about but I still get syntax errors. My only excuse is that I've not had a coffee yet... – sr28 Apr 29 '15 at 08:06
You don't want a comma after TOP 0, and INTO has to come before FROM. I will edit to make it clearer – Caleth Apr 29 '15 at 08:35
Thanks. Very useful. I wish I could just edit the tooltip though as to what data was displayed. – sr28 Apr 30 '15 at 07:48

score 1 · Answer 3 · answered Apr 29 '15 at 06:39

1

Since your function appears to consist of just an INSERT ... SELECT and RETURN, you could change it to an inline TVF. That would not really answer your question as asked but it would probably solve the underlying problem of needing to know the exact data types in the first place:

DROP FUNCTION [dbo].[MyReportFunction]
-- multi-statement TVF and inline TVF are different object types,
-- so ALTER will not work, you have to drop and re-create the object
GO

CREATE FUNCTION [dbo].[MyReportFunction]
(
    @userid        varchar(255),
    @location      varchar(255),
    more params here...
)

RETURNS TABLE
AS
RETURN
(
    SELECT tblA.Title, tblB.Location, more columns...
    FROM TableA tblA
    INNER JOIN TableB tblB
    ON tblA.Id = tblB.Id
    WHERE tblB.Location = @location
)

Not only will you stop worrying about explicit data type declaration as a result of this switch, you will also get a function that is more transparent to the query optimiser. If the query that uses the function is complex, not just a mere SELECT * FROM dbo.MyReportFunction(...), the optimiser will be able to blend the function's query with the main query to get a better execution plan.

answered Apr 29 '15 at 06:39

Andriy M

76,112
17
94
154

Would I not need permissions to create the tvp type first? I'm not sure I have that. If I did I'm pretty sure the DBA's wouldn't want me to be doing this. However I can see the benefits. – sr28 Apr 29 '15 at 07:59
Hm, I wasn't talking about data types but about object types (or perhaps I should have said, object kinds). So, this is not about TVPs, just TVFs (table-valued functions). Both yours and mine are TVFs, only yours is multi-statement and mine is inline. For SQL Server they are different kinds of object, which is why you have to DROP your multi-statement function and CREATE it again as an inline one (as opposed to just ALTERing it). Sorry about this confusion. – Andriy M Apr 29 '15 at 08:18
Anyway, I didn't consider the permission issue at all in my answer. If you don't have a permission to create functions either, this might indeed not work for you. Sorry again. – Andriy M Apr 29 '15 at 08:20
No, my fault for not reading it properly. I saw TVP instead of TVF and immediately went off and researched a bit more. I've got permissions to create functions and sp's so this would work. I'll look at it some more. – sr28 Apr 29 '15 at 08:21

score 0 · Answer 4 · answered Apr 28 '15 at 09:37

0

Use SQL_VARIANT_PROPERTY:

SELECT top 1
    SQL_VARIANT_PROPERTY(colname, 'BaseType') BaseType, 
    SQL_VARIANT_PROPERTY(colname, 'Precision') [Precision], 
    SQL_VARIANT_PROPERTY(colname, 'Scale') Scale, 
    SQL_VARIANT_PROPERTY(colname, 'MaxLength') [MaxLength]
FROM yourtable

Result could be:

BaseType  Precision  Scale  MaxLength
int       10         0      4

answered Apr 28 '15 at 09:37

t-clausen.dk

43,517
12
59
92

This is useful info, but still fairly cumbersome. I'd still need to alter this query for every column I'm not sure of the size (lots!) and for each table as well, which by the end of it I probably would have been better off scrolling to the table/s and looking at the columns. I'm starting to get the impression this can't be done as something 'quick and easy'. – sr28 Apr 28 '15 at 09:42

Is there a quick way to view the size of an existing column when writing a query?

4 Answers4