SQL View Columns

Question

This is probably a strange question... Hopefully, someone enjoys the more esoteric portion of SQL. :)

I have some views with many columns. The column widths are obstructive to reviewing the data. Once a column width is manually adjust and the view is saved, the column is perpetually saved at that width unless manually changed and again saved.

This makes me wonder how SQL saves column widths. Is there a table containing that data? If so, could an updated query revise all the columns for a given table to a defined width? If so, how?

Just FYI, my DBA and I spent a good while trying to figure this out. I am not asking without first endeavoring to solve it myself. I am a novice with SQL and hope for more experienced guidance.

This isn't really a SQL Server question. What program are you using to display your data? (Also, 'view' is a specific SQL concept that doesn't pertain to column widths) — Anon, Dec 03 '13 at 19:40
I primarily use BIDS to interface with SQL. A 'view' is the object with which the data was queried. I am unclear on the differences between a view and a query, but I believe a view cannot alter data. Either way, I believe the question applies to both views and queries. — Uziel, Dec 03 '13 at 22:59

score 1 · Answer 1 · answered Dec 03 '13 at 19:39

They are stored in the extended properties. You can review them either in the properties window for the view in question or by scripting them out. If scripting out you need to ensure that you have selected the option to script extended properties hidden behind the advanced button. You can also set the default options in the options of SSMS. Note that if you have not used the view designer there will be no extended properties.

Bogdan Sahlean · Answer 2 · 2013-12-04T04:45:54.830

Solution: use sp_refreshview (this demo shows that SQL Server doesn't automatically update metadata information for dbo.MyView after I change the maximum length for dbo.MyTable.Col2 and we have to use sp_refreshview [or ALTER VIEW, DROP & CREATE] to update metadata for the view):

IF EXISTS(SELECT * FROM sys.views t WHERE t.object_id = OBJECT_ID(N'dbo.MyView'))
BEGIN
    DROP VIEW dbo.MyView;
END
IF EXISTS(SELECT * FROM sys.tables t WHERE t.object_id = OBJECT_ID(N'dbo.MyTable'))
BEGIN
    DROP TABLE dbo.MyTable;
END
CREATE TABLE dbo.MyTable
(
    Col1 INT IDENTITY(1,1) PRIMARY KEY,
    Col2 VARCHAR(3) NULL
);
GO
CREATE VIEW dbo.MyView
AS
SELECT * FROM dbo.MyTable;
GO

SELECT  cols.COLUMN_NAME, cols.DATA_TYPE, cols.CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS cols 
WHERE   cols.TABLE_SCHEMA = 'dbo'
AND     cols.TABLE_NAME = 'MyView'
AND     cols.COLUMN_NAME = 'Col2'
GO
/*
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH
----------- --------- ------------------------
Col2        varchar   3
*/

ALTER TABLE dbo.MyTable
ALTER COLUMN Col2 VARCHAR(10);
GO

SELECT  cols.COLUMN_NAME, cols.DATA_TYPE, cols.CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS cols 
WHERE   cols.TABLE_SCHEMA = 'dbo'
AND     cols.TABLE_NAME = 'MyView'
AND     cols.COLUMN_NAME = 'Col2'
GO
/*
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH
----------- --------- ------------------------
Col2        varchar   3
*/

EXEC sp_refreshview 'dbo.MyView';
GO
SELECT  cols.COLUMN_NAME, cols.DATA_TYPE, cols.CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS cols 
WHERE   cols.TABLE_SCHEMA = 'dbo'
AND     cols.TABLE_NAME = 'MyView'
AND     cols.COLUMN_NAME = 'Col2'
GO
/*
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH
----------- --------- ------------------------
Col2        varchar   10
*/

As far as I can tell, 'CHARACTER_MAXIMUM_LENGTH' does not affect column width. --- Running the query below then adjusting the column width and saving the table, then again running the query produces the same result. SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS AS cols WHERE (TABLE_SCHEMA = 'dbo') AND (TABLE_NAME = 'vTest') — Uziel, Dec 04 '13 at 00:44
This demo shows only that SQL Server doesn't update dbo.MyView metadata information when that base tables (columns) are changed. After I changed the column maximum length you can see that without sp_refreshview / alter view / drop & create the dbo.MyView metadata aren't automatically updated. Could be useful if you provide a repro script. You didn't provided enough infos to reproduce your problem. Did you executed sp_refreshview ? — Bogdan Sahlean, Dec 04 '13 at 04:31
@Uziel: After refreshing the `dbo.MyView` metadata using one of previous methods (ex. sp_refresh) you need to refresh also the dataset/recordset within BIDS project. — Bogdan Sahlean, Dec 04 '13 at 09:15

SQL View Columns

2 Answers2