June 17, 2013 at 7:42 pm
A simpler way to avoid "Select * " is to use "for xml path" to get columns:
SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS SELECT '
+ STUFF((
SELECT ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=isc.TABLE_NAME
FOR XML PATH('')),1,1,'')
+ ' FROM '
+ TABLE_SCHEMA +'.'+ TABLE_NAME + ' SCHEMA_BINDING'
FROM INFORMATION_SCHEMA.TABLES isc
where TABLE_TYPE='BASE TABLE'
October 27, 2014 at 10:39 am
andrew.mckee (6/17/2013)
A simpler way to avoid "Select * " is to use "for xml path" to get columns:SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS SELECT '
+ STUFF((
SELECT ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=isc.TABLE_NAME
FOR XML PATH('')),1,1,'')
+ ' FROM '
+ TABLE_SCHEMA +'.'+ TABLE_NAME + ' SCHEMA_BINDING'
FROM INFORMATION_SCHEMA.TABLES isc
where TABLE_TYPE='BASE TABLE'
Without getting in to whether or not view generation is a good idea for the reasons mentioned in the original post (e.g. SSRS, locking, security etc.) I believe that this last post is the best answer.
Naming the columns is the safest bet.
If you want to keep the views then adding a little formatting wouldn't go amiss [use char(10)] and I am not sure whether or not it might be a good idea to add an order by to the column list. It might be implied already depending on how INFORMATION_SCHEMA orders itself but it cannot hurt to enforce it.
SELECT 'CREATE VIEW ' + TABLE_SCHEMA + '.vw_' + TABLE_NAME + ' AS'+ CHAR(10)
+'SELECT ' + CHAR(10)
+ STUFF((
SELECT ',' + COLUMN_NAME + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=isc.TABLE_NAME
ORDER BY ORDINAL_POSITION
FOR XML PATH('')),1,1,'')
+ ' FROM '
+ TABLE_SCHEMA +'.'+ TABLE_NAME + ' SCHEMA_BINDING' + CHAR(10) + 'GO' + CHAR(10)
FROM INFORMATION_SCHEMA.TABLES isc
where TABLE_TYPE='BASE TABLE'
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
October 27, 2014 at 11:11 am
David,
Note that this is a 2 year old thread. That said, your code can be improved to avoid errors if table or column names have spaces or other characters. You also missed the schema comparison to prevent problems with tables with the same names on different schemas.
Here's a modification of your code.
SELECT 'CREATE VIEW ' + QUOTENAME( Table_Schema ) + '.' + QUOTENAME( 'vw_' + Table_Name )
+ ' AS' + CHAR( 10 )
+ 'SELECT ' + CHAR( 10 )
+ STUFF((
SELECT ' ,' + QUOTENAME( C.Column_Name ) + CHAR( 10 )
FROM Information_Schema.Columns C
WHERE C.Table_Name = Isc.Table_Name
AND C.Table_Schema = Isc.Table_Schema
ORDER BY Ordinal_Position
FOR XML PATH( '' )), 1, 5, ' ' )
+ ' FROM ' + QUOTENAME( Table_Schema ) + '.' + QUOTENAME( Table_Name )
+ ' SCHEMA_BINDING ' + CHAR( 10 )
+ 'GO' + CHAR( 10 )
FROM Information_Schema.Tables Isc
WHERE Table_Type = 'BASE TABLE';
October 28, 2014 at 2:23 pm
In case anyone thinks this example is a good idea I just want to add my vote to what Sean already suggested, namely: Never, never use SELECT * FROM a table in a view. Using SELECT * like that is a sure route to a whole lot of problems.
+ a billion
Don Simpson
October 28, 2014 at 9:21 pm
DonlSimpson (10/28/2014)
In case anyone thinks this example is a good idea I just want to add my vote to what Sean already suggested, namely: Never, never use SELECT * FROM a table in a view. Using SELECT * like that is a sure route to a whole lot of problems.
+ a billion
I have to say that "It Depends". Yes, I understand that a view can return the wrong information for a column if SELECT * is used and an "intermediate" column is added but for really wide views (especially from a single table), SELECT * can provide a fair bit of performance difference compared to listing, say, 100 columns. The way to prevent the problem of someone missing the "column shift" problem if someone changes the underlying table is to create the table using WITH SCHEMABINDING to force someone to do something with the view before they change the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 28, 2014 at 9:39 pm
Jeff Moden (10/28/2014)
[...]The way to prevent the problem of someone missing the "column shift" problem if someone changes the underlying table is to create the table using WITH SCHEMABINDING to force someone to do something with the view before they change the table.
...which prevents SELECT * as a handy side-effect. If you try:
Msg 1054, Level 15, State 6, Procedure X, Line 1
Syntax '*' is not allowed in schema-bound objects.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 29, 2014 at 9:10 am
Paul White (10/28/2014)
Jeff Moden (10/28/2014)
[...]The way to prevent the problem of someone missing the "column shift" problem if someone changes the underlying table is to create the table using WITH SCHEMABINDING to force someone to do something with the view before they change the table....which prevents SELECT * as a handy side-effect. If you try:
Msg 1054, Level 15, State 6, Procedure X, Line 1
Syntax '*' is not allowed in schema-bound objects.
Heh... gotta love it. Obviously, I don't use views much. Thank you for the correction, Paul.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 22, 2016 at 8:30 pm
After reading all of the posts here, I put together this script which combines the best of the posts and adds a little more. This script is for making views in a "Destination" DB using tables in a "Source" DB. It needs to be run in the context of the Destination DB.
It makes views out of all of the tables in the specified SourceDB & SourceSchema.
declare @SourceDB nvarchar(max), @SourceSchema nvarchar(max), @DestSchema nvarchar(max);
set @SourceDB = 'MySourceDB';
set @SourceSchema = 'mySourceSchema';
set @DestSchema = 'myDestSchema';
declare @TableInfo table
(
Table_Schema nvarchar(max),
Table_Name nvarchar(max),
Column_Name nvarchar(max),
Ordinal_Position int
);
declare @sql1 nvarchar(max);
set @sql1 =
'select c.Table_Schema, c.Table_Name, c.Column_Name, c.Ordinal_Position
from ' +
QUOTENAME(@SourceDB) + '.INFORMATION_SCHEMA.TABLES t inner join ' + QUOTENAME(@SourceDB) + '.INFORMATION_SCHEMA.COLUMNS c on
t.Table_Schema = c.Table_Schema and
t.Table_Name = c.Table_Name
where t.Table_Type = ''BASE TABLE''' +
case when @SourceSchema is not null then ' and c.Table_Schema = ''' + @SourceSchema + '''' else '' end +
'group by c.Table_Schema, c.Table_Name, c.Column_Name, c.Ordinal_Position;'
;
insert into @TableInfo
exec sp_executesql @sql1;
declare @CreateViewStatements table
(
Id int identity(1,1),
Sql nvarchar(max)
)
insert into @CreateViewStatements (Sql)
SELECT
'IF EXISTS (SELECT * FROM sys.views
WHERE object_id = OBJECT_ID('''+ isnull(@DestSchema, TABLE_SCHEMA) +'.view_'+TABLE_NAME+'''))
DROP VIEW ['+isnull(@DestSchema, TABLE_SCHEMA)+'].[view_'+TABLE_NAME+'];
EXEC(''CREATE VIEW ' + QUOTENAME( isnull(@DestSchema, TABLE_SCHEMA) ) + '.' + QUOTENAME( 'view_' + Table_Name )
+ ' AS' + CHAR( 10 )
+ 'SELECT ' + CHAR( 10 )
+ STUFF((
SELECT ' ,' + QUOTENAME( t2.Column_Name ) + CHAR( 10 )
FROM @TableInfo t2
WHERE t2.Table_Name = t.Table_Name
AND t2.Table_Schema = t.Table_Schema
ORDER BY Ordinal_Position
FOR XML PATH( '' )), 1, 5, ' ' )
+ ' FROM ' + QUOTENAME(@SourceDB) + '.' + QUOTENAME( Table_Schema ) + '.' + QUOTENAME( Table_Name )
+ ' SCHEMA_BINDING ' + CHAR( 10 ) + ''');'
as Sql
FROM @TableInfo t
group by t.Table_Schema, t.Table_Name;
declare @n int, @viewCount int;
set @n = 1;
select @viewCount = count(*) from @CreateViewStatements;
DECLARE @sql As NVarchar(MAX);
while @n <= @viewCount
begin
select @sql = v.Sql from @CreateViewStatements v where v.Id = @n;
print @sql;
exec(@sql);
set @n = @n + 1;
end
January 26, 2016 at 3:24 pm
I'd strongly urge you to avoid the INFORMATION_SCHEMA views. Instead, stick to the sys. views.
Not only are I_S views deficient in newer columns vs. the system tables, they are slow and lock/deadlock prone. Ah, you say, but when you list the definition of those views, it doesn't look that way. I know, but we have legacy issues with this all the time. I also believe there were cases where the I_S views didn't list certain objects, but it's been a while on that one so it may be resolved by now.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 29, 2018 at 7:39 am
One alternative to avoid INFORMATION_SCHEMA and use sys.tables and sys.columns instead , but for generation of views inside the same database than tables but using different schemas (that must exists).
The store procedure use DROP VIEW IF EXISTS synthax (so for 2016+ only or you will need to change this part)
CREATE Procedure [usp_generate_view_script] (@SourceSchema VARCHAR(100) ,@TargetSchema VARCHAR(100))
AS
BEGIN
DECLARE @Columns VARCHAR(MAX) = '';
DECLARE @Schema VARCHAR(100);
DECLARE @Table VARCHAR(200);
DECLARE @TableID INT;
DECLARE @SQLSELECT NVARCHAR(MAX)
DECLARE @SQLCommand NVARCHAR(MAX);
--CURSOR
DECLARE curTables CURSOR LOCAL
FOR SELECT SCHEMA_NAME(schema_id) TABLE_SCHEMA, name TABLE_NAME, object_id
FROM sys.tables
where TYPE='U' and SCHEMA_NAME(schema_id)=@SourceSchema
FOR READ ONLY;
OPEN curTables;
FETCH NEXT FROM curTables INTO @Schema,@Table,@TableID;
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @Columns='';
SELECT
@Columns = @Columns + COALESCE('[' + NAME + ']' + ',', '')
FROM
sys.columns
WHERE
object_id = @TableID
ORDER BY
column_id;
SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns) - 1);
SET @SQLSELECT = N'SELECT ' + @Columns + ' FROM [' + @Schema + '].[' + @Table +'];';
--PRINT @Sql
PRINT @SQLCommand;
BEGIN TRY
SET @SQLCommand = N'DROP VIEW IF EXISTS [' + @TargetSchema + '].[' + @Table + '];';
EXEC sp_executesql @statement=@SQLCommand ;
SET @SQLCommand = N'CREATE VIEW [' + @TargetSchema + '].[' + @Table + '] AS ' + @SQLSELECT;
PRINT @SQLCommand;
EXEC sp_executesql @statement=@SQLCommand ;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
FETCH NEXT FROM curTables INTO @Schema,@Table,@TableID
END
DEALLOCATE curTables
END
October 28, 2019 at 3:46 pm
You ABSOLUTELY do not want to use SELECT *.
That alone with cause all kinds of performance problems. There are multiple articles explaining why. You will have to generate the list of columns to select.
Theoretically one could generate the CREATE TABLE scripts for all the tables you want, then change the CREATE TABLE to CREATE VIEW, and then use Notepad++ to create editing macros to remove the parts you don't want, such as data types, primary key defs, and other add-on scripts such as for constraints.
Or you could use Python or Powershell to clean up the scripts.
October 28, 2019 at 5:23 pm
Getting back to the original replies on this thread, which more than adequately expressed the right idea which I'll strongly second, this exercise is a total waste of time and will not prevent any slowdowns for reporting. Whether you use SELECT * or not, it may actually cause several performance and resource usage problems but, even if it doesn't, this drill is a total waste of time and resources for the original problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2019 at 8:07 am
SELECT 'CREATE VIEW ' + tb.TABLE_SCHEMA + '.vw' + tb.TABLE_NAME + ' AS SELECT '+
(SELECT STUFF((SELECT ',t.' + vp.Column_Name FROM information_schema.columns vp
WHERE vp.Table_Name=tb.TABLE_NAME and vp.Table_Schema=tb.TABLE_SCHEMA FOR XML PATH('')), 1, 1, ''))
+' FROM '
+ tb.TABLE_SCHEMA +'.'+ tb.TABLE_NAME + ' t with(nolock)'
FROM INFORMATION_SCHEMA.TABLES tb
where TABLE_TYPE='BASE TABLE'
October 31, 2019 at 8:48 am
Links are not accessible 🙂 I'm quite interested in reading it.
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply