Wow! I didn’t realize that I had missed a week posting. I wasn’t intending to post daily, still…. My excuse is that I had a board meeting in Albuquerque last week that forced me in to a last minute review to make sure my system was working properly and loaded on to my laptop correctly, plus travel. And even with that prep, I still had a bug creep in. Still, my presentation and demo went quite well and the bug was somewhat minor.
ANYWAY, today I have a code snippet that I just wrote. I love working with base tables in SQL Server, and needed all of the field names of my largest (field count-wise) table, some 50 fields. So I wrote this code to give me all of the field names so that I would’t be doing a SELECT * in a view.
If you want a table prefix to appear in front of each field name, change the @tblpre variable. Run this code with the output window set to Text, copy and paste, delete the row of dashes, and you should be good.
--GetTableFieldNames.sql, WW, 04/28/2015 --returns all field names with some exclusions: --makes it easier to add a long list of field names --to a view to eliminate SELECT * declare @tblpre char(3) = '' set nocount on select ', ' + rtrim(@tblpre) + sc.name from sys.columns sc join sys.tables st on sc.object_id = st.object_id and st.name = '[TABLE NAME TO GET FIELDS FOR]' where sc.is_identity = 0 and sc.is_computed = 0 and system_type_id <> 189 --exclude timestamps set nocount off