October 31, 2019 at 8:55 am
INFORMATION_SCHEMA views are perhaps not so much in vogue as they were:
Links are not accessible 🙂
October 31, 2019 at 2:07 pm
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'
This works but the introduction of that awful NOLOCK hint is a really a bad decision. That hint comes with a LOT of baggage and putting it on a view that is updateable is really horrific. It can and will do fun things like corrupt indexes. https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere If you remove the NOLOCK portion of this I like that it is set based.
The other issue here is using INFORMATION_SCHEMA.TABLES to determine the schema. The schema is inconsistent in there as those views are for backwards compatibility only. You should use sys.objects and sys.tables instead. Even MS says not to trust the schema in that view. https://docs.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/tables-transact-sql?view=sql-server-ver15
But major kudos for offering a set based solution instead of the dreaded RBAR version so many people will post.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 31, 2019 at 3:36 pm
laurie-789651 wrote:INFORMATION_SCHEMA views are perhaps not so much in vogue as they were:
Links are not accessible 🙂
He changed his URLs a few months ago. That article is now here. https://sqlblog.org/2011/11/03/the-case-against-information_schema-views
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2020 at 4:12 pm
Hi,
I also need to Create View Script for all tables for report purposes. Tables get updated daily. I can't afford any crashing.
I am a novice in SQL, I would appreciate the help.
The script below works fantastic it extracts all data as I need. Nevertheless, when you create SQL view you can't use declare.
How can I rewrite the query below to be able to use it in View?
DECLARE @SelectClause VARCHAR(100) = 'SELECT [Colum1], [colum2],[Colum3],[colum4],[colum5]'
,@Query VARCHAR(1000) = ''
SELECT @Query = @Query + @SelectClause + ' FROM ' + TABLE_NAME + ' UNION '
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE '%Table1' OR TABLE_NAME LIKE '%table2')
SELECT @Query = LEFT(@Query, LEN(@Query) - LEN(' UNION '))
EXEC (@Query)
Thank you,
November 19, 2020 at 4:17 pm
Views don't take parameters, which I suspect is what @Table1 and @Table2 are in your example. You will need to write it as a function or a stored procedure instead. And then you need to be very careful to guard against SQL injection.
John
November 19, 2020 at 5:20 pm
I presume I can just union tables? I extract them by the query below so them I am sure all are included.
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_NAME LIKE 'abcd%' )
order by TABLE_NAME
Thank you,
November 19, 2020 at 5:47 pm
Why do you feel you need views of all the tables? Are you trying to return a fixed subset of the rows in the table? If it's for reporting, then why not use stored procedures to filter the data? You can't pass parameters to a view and filter dynamically, so they're not super helpful if you're doing reporting.
You could create a schema for reporting and put all the stored procedures you need to use for reporting in them and then just grant execute rights to those to a group of users... seems easier than what you're planning.
November 19, 2020 at 6:14 pm
Sure you're perfectly right :
CREATE OR ALTER PROCEDURE [usp_generate_view_script]
(
@SourceSchema VARCHAR(100),
@TargetSchema VARCHAR(100)
)
-- Usage EXEC [usp_generate_view_script] @SourceSchema='dbo', @TargetSchema='views';
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(QUOTENAME(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 + N' FROM ' + QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table) + N';';
--PRINT @Sql
PRINT @SQLCommand;
BEGIN TRY
SET @SQLCommand = N'DROP VIEW IF EXISTS ' + QUOTENAME(@TargetSchema) + N'.' + QUOTENAME(@Table) + N';';
EXEC sp_executesql @statement = @SQLCommand;
SET @SQLCommand = N'CREATE VIEW ' + QUOTENAME(@TargetSchema) + N'.' + QUOTENAME(@Table) + N' 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;
Viewing 8 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply