October 2, 2012 at 8:03 am
charles.byrne (10/2/2012)
Besides the .* issue which sounds like you plan on fixing. What was also odd was the naming prefix for the views being 'tbl_' for a view. When I see that prefix on a database object I think that it is a table not a view. If you were to prefix a view shouldn't you use 'vw_' or 'v_' or even 'qry_'?To follow your intent of a base view you could even prefix 'vw_base_' or something similar.
If I see [Employee] and [tbl_Employee] in sql or code I would make the assumption that tbl_Employee is actually the table and Employee is a view.
It would certainly make things less confusing for a DBA or a programmer not familiar with the database. Just another suggestion.
Or even better, just drop the prefix entirely. Object prefixes are one of my biggest pet peeves.
_______________________________________________________________
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 2, 2012 at 8:18 am
And if you are going to have to modify column names after you create them I don't see how this saves you any effort or time. At any rate instead of using a cursor and making your code complicated why not just build the string you want to execute and then execute it.
select 'CREATE VIEW [' + TABLE_SCHEMA + '].[v' + TABLE_NAME + '] AS SELECT [' + TABLE_SCHEMA + '].[' + Table_name + '].* FROM [' + TABLE_SCHEMA + '].[' + Table_name +'];'
from INFORMATION_SCHEMA.tables
where TABLE_TYPE = 'base table'
order by table_name
This will give you the create view statement for all of the views. Then you can modify or remove any of them before you create them.
_______________________________________________________________
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 2, 2012 at 8:30 am
Sean Lange (10/2/2012)
Or even better, just drop the prefix entirely. Object prefixes are one of my biggest pet peeves.
I would agree with you except for name collision. My bet having a table called [Employee] and a view called [Employee] would give any parser fits. It's also not allowed.
ATBCharles Kincaid
October 2, 2012 at 9:15 am
The intro to this article in the daily newsletter said:
"This article talks about a few issues with using views too extensively in your design."
It doesn't say anything AT ALL about that. Instead it gives a script to automate a view that "SELECT * FROM...." Really the opposite of the title.
October 2, 2012 at 10:04 am
There are so many WTF's here. First, the argument where you can only access through views has been thoroughly debated and debunked, it shouldn't be held to high here, so the reason for a script like this seems superfluous.
Second, if a script is posted to do something, it needs to be proofed. Select * in this day and age is silly, regardless of whether it was the focus or not. How many people fault Microsoft for the same silly mistakes in their documentation?
Third, bait and switch. I expected an article telling me the pro's and/or con's of using views extensively in my architecture, instead I got this.
My expectations were not met, I want my money back.
October 2, 2012 at 10:13 am
weirdbeard (10/2/2012)
I followed the link from the SqlErvercentral email, which described this article as"This article talks about a few issues with using views too extensively in your design."
Which it clearly doesn't do, in fact it does the opposite.
October 2, 2012 at 10:56 am
Charles Kincaid (10/2/2012)
Sean Lange (10/2/2012)
Or even better, just drop the prefix entirely. Object prefixes are one of my biggest pet peeves.I would agree with you except for name collision. My bet having a table called [Employee] and a view called [Employee] would give any parser fits. It's also not allowed.
Which is also why this technique is rather pointless. It provides no tangible benefit.
_______________________________________________________________
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 2, 2012 at 12:18 pm
Hello all,
I see some excellent questions, suggestions, concerns and advise.
here are some of the answers that might come handy and helpful and I hope that we are all learning from each other today. I am speaking for myself too of course. 😉
> yes, table prefix is not needed (see code in below) if you are creating your schema and role of course, makes it easier for the codes and DBA.. All has to be changed in a code would be the schema. As you know, you cannot have views to tables without prefix, as you know, in the same schema.
> select *.. I agree. Although, when careating the view, the design view of the view shows the individual fields accordingly to the ordinal positions. If there are changes in the table you would have to revisit or re-run the process anyway, whether you have a select * or not. These views will not add / update / modify any table if you configure your schema and role properly.
> additionally, when you have multiple tables having field names of the same, such as LName, FName, Address, etc. that may appear in more than one table then it would be helpful to
add the table name as a prefix to the field name and I am sure few of you will agree and appreciate that.
> use of sp_msforeachtable; of course if you can cautiously use this method and do what this code in below is capable of doing then by all means knock yourself out.
> using schemas and roles will give you a better grip on some of the horizontally partitioning tasks
declare @Prefix nvarchar(10)
set @Prefix = ''
-- 4 local variable for each record on a table 4 part naming convention
declare @TABLE_CATALOG nvarchar(64), @TABLE_SCHEMA nvarchar(64), @TABLE_NAME nvarchar(64)
-- The string declaration in here is for the TSQL statement that will be executed to create each view. 2000, may be an over-kill but this is up to you too
declare @ExecString nvarchar(max)
set @ExecString = ''
-- This sample shows that there were 71 table name, not a big of a deal for a cursor and will not "crash" your system.. I know how you feel about cursor ;)
declare MyTables cursor for
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.tables
where TABLE_TYPE = 'base table'
order by table_name
--open the newly created cursor and grab the first record.
open MyTables
fetch next from MyTables into
@TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
-- start of the loop process and will continue until no more records in there to process
while @@FETCH_STATUS = 0
begin
declare @Column_name nvarchar(128)
declare @Column_Name_String nvarchar(max)
set @Column_Name_String = ''
declare MyColumns cursor for
select column_name
from information_schema.columns
where table_name = @table_name
order by ordinal_position
open MyColumns
fetch next from MyColumns into
@column_name
while @@FETCH_STATUS = 0
begin
select @Column_Name_String = @Column_Name_String + '['+@Column_Name+']' + ' As ' + @table_name+'_'+@Column_name+', '
fetch next from MyColumns into
@column_name
end
close MyColumns
deallocate MyColumns
set @Column_Name_String = SUBSTRING(@Column_Name_String, 1, len(@Column_Name_String)-1)
print @column_name_string
set @ExecString += 'CREATE VIEW [ContractorSchema].['+@Prefix+@TABLE_NAME + '] AS SELECT ' + @Column_Name_String + ' FROM [' + @TABLE_SCHEMA + '].[' + @Table_name +']'
set @Column_Name_String = ''
exec(@execstring)
fetch next from MyTables into
@TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME
set @ExecString = ''
set @Column_Name_String = ''
end
-- wrap it up by destroying the objects
close MyTables
deallocate MyTables
Cheers,
John Esraelo
October 2, 2012 at 1:13 pm
DiverKas (10/2/2012)
There are so many WTF's here. First, the argument where you can only access through views has been thoroughly debated and debunked, it shouldn't be held to high here, so the reason for a script like this seems superfluous.Second, if a script is posted to do something, it needs to be proofed. Select * in this day and age is silly, regardless of whether it was the focus or not. How many people fault Microsoft for the same silly mistakes in their documentation?
Third, bait and switch. I expected an article telling me the pro's and/or con's of using views extensively in my architecture, instead I got this.
My expectations were not met, I want my money back.
DiverKas,
I will check the article and the code and make sure nothing is missing on the pros and cons. Perhaps it was edited and dropped out.
What I like to add is that you need to remain professional and refrain from using unnecessary language.
We are all here to learn from each other and excel.
Thank you for your comments and suggestions and I will get back after checking the content.
Apologies for inconvenience.
Cheers,
John Esraelo
October 2, 2012 at 1:25 pm
DavidL (10/2/2012)
The intro to this article in the daily newsletter said:"This article talks about a few issues with using views too extensively in your design."
It doesn't say anything AT ALL about that. Instead it gives a script to automate a view that "SELECT * FROM...." Really the opposite of the title.
DavidL,
Apologies to the "teaser" title. "This article talks.. " should not have been there and you are all right. Honestly, did not notice that until it was approved and published.
Please accept my sincere apology and also I have altered the code on page 3 of the forum discussions on select * and added a paragraph or 2 to add columns.
Thank you for pointing this out.
Cheers,
John Esraelo
October 2, 2012 at 1:27 pm
additionally, when you have multiple tables having field names of the same, such as LName, FName, Address, etc. that may appear in more than one
table then it would be helpful to
add the table name as a prefix to the field name and I am sure few of you will agree and appreciate that.
NO, this is the main reason I abhor prefixes so much. Why should a customer's email be CustEmail or Customer_Email because it is in the Customers table? It is still just Email. If there is ambiguity because the same column name exists in more than 1 table it is far preferable to use a table alias instead.
Do you use prefixes for foreign keys too? Just because there is more than 1 column with the same name does not mean you need to make it harder to work with.
_______________________________________________________________
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 2, 2012 at 1:33 pm
Sean Lange (10/2/2012)
additionally, when you have multiple tables having field names of the same, such as LName, FName, Address, etc. that may appear in more than one
table then it would be helpful to
add the table name as a prefix to the field name and I am sure few of you will agree and appreciate that.
NO, this is the main reason I abhor prefixes so much. Why should a customer's email be CustEmail or Customer_Email because it is in the Customers table? It is still just Email. If there is ambiguity because the same column name exists in more than 1 table it is far preferable to use a table alias instead.
Do you use prefixes for foreign keys too? Just because there is more than 1 column with the same name does not mean you need to make it harder to work with.
Sean,
Actually when the view is created it does create and alias as you can see.. it is again up to the reader of the code or programmer what to alias and to name.
I am not trying to suggest a copy and paste from here to elsewhere..
you guys make your own changes and implement the way you want the code to work for you..
This code is merely suggesting a method to automate some of the processes to make life easier.
The readers can read, decide, discard and refrain from some of unnecessary language as you might have noticed in here.
Again, we are all learning..
Cheers,
John Esraelo
October 2, 2012 at 1:41 pm
Sean Lange (10/2/2012)
additionally, when you have multiple tables having field names of the same, such as LName, FName, Address, etc. that may appear in more than one
table then it would be helpful to
add the table name as a prefix to the field name and I am sure few of you will agree and appreciate that.
NO, this is the main reason I abhor prefixes so much. Why should a customer's email be CustEmail or Customer_Email because it is in the Customers table? It is still just Email. If there is ambiguity because the same column name exists in more than 1 table it is far preferable to use a table alias instead.
Do you use prefixes for foreign keys too? Just because there is more than 1 column with the same name does not mean you need to make it harder to work with.
Perhaps if you had seen some DBs such as Hyland's OnBase system that has over 3500+ tables and thousands of fields that once they joined for reporting it would make life a hell trying to figure out which field came from which table.
Try building a MART around those.. For those of us that are data miners then totally understand what I am referring to.
Cheers,
John Esraelo
October 2, 2012 at 1:55 pm
John Esraelo-498130 (10/2/2012)
Sean Lange (10/2/2012)
additionally, when you have multiple tables having field names of the same, such as LName, FName, Address, etc. that may appear in more than one
table then it would be helpful to
add the table name as a prefix to the field name and I am sure few of you will agree and appreciate that.
NO, this is the main reason I abhor prefixes so much. Why should a customer's email be CustEmail or Customer_Email because it is in the Customers table? It is still just Email. If there is ambiguity because the same column name exists in more than 1 table it is far preferable to use a table alias instead.
Do you use prefixes for foreign keys too? Just because there is more than 1 column with the same name does not mean you need to make it harder to work with.
Perhaps if you had seem some DBs such as Hyland's OnBase system that has over 3500+ tables and thousands of fields that once they joined for reporting it would make life a hell trying to figure out which field came from which table.
Try building a MART around those.. For those of us that are data miners then totally understand what I am referring to.
I totally understand what you are saying. For situations like that I still prefer to use reasonable column names. It is up to the developer to add some meaning to the column via a column alias when appropriate. That to me makes much more sense then changing column names because it might make something easier.
I would suggest that with 3500+ tables adding a prefix does not make the ambiguity any less, it just changes where the lookup has to occur. Now instead of wondering what table it comes from, you have to figure out which table has a given column name. Same challenge, different approach to finding the answer.
This debate of prefixes has being going round and round in the database and programming world for a long time. There is no absolute best approach to this stuff. Both ways have their ups and downs. The only absolute in sql server is "It depends". 😀
_______________________________________________________________
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 2, 2012 at 2:01 pm
Sean Lange (10/2/2012)
John Esraelo-498130 (10/2/2012)
Sean Lange (10/2/2012)
additionally, when you have multiple tables having field names of the same, such as LName, FName, Address, etc. that may appear in more than one
table then it would be helpful to
add the table name as a prefix to the field name and I am sure few of you will agree and appreciate that.
NO, this is the main reason I abhor prefixes so much. Why should a customer's email be CustEmail or Customer_Email because it is in the Customers table? It is still just Email. If there is ambiguity because the same column name exists in more than 1 table it is far preferable to use a table alias instead.
Do you use prefixes for foreign keys too? Just because there is more than 1 column with the same name does not mean you need to make it harder to work with.
Perhaps if you had seem some DBs such as Hyland's OnBase system that has over 3500+ tables and thousands of fields that once they joined for reporting it would make life a hell trying to figure out which field came from which table.
Try building a MART around those.. For those of us that are data miners then totally understand what I am referring to.
I totally understand what you are saying. For situations like that I still prefer to use reasonable column names. It is up to the developer to add some meaning to the column via a column alias when appropriate. That to me makes much more sense then changing column names because it might make something easier.
I would suggest that with 3500+ tables adding a prefix does not make the ambiguity any less, it just changes where the lookup has to occur. Now instead of wondering what table it comes from, you have to figure out which table has a given column name. Same challenge, different approach to finding the answer.
This debate of prefixes has being going round and round in the database and programming world for a long time. There is no absolute best approach to this stuff. Both ways have their ups and downs. The only absolute in sql server is "It depends". 😀
Sean,
I cannot agree with you more. And that was the reason why for me to say what I said.. Unfortunately, not everyone is thinking like you do and there are some folks, perhaps not on this thread, expect to get a "ready to work" code and call it day. There are websites for that matter.. for instance... http://www.expert-exchange.com a fantastic site for that matter. ..
In fact, I am subscribing to that site and hoping to learn more about shell scripting and folks over there actually give the actual working code. Of course, there is a small annual fee involved in there, but, it is what it is.
Cheers,
John Esraelo
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply