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.

Answer 1

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.

Answer 2

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
*/