October 9, 2015 at 3:00 am
Very often we need a list of colums for select, insert, update, or unique names for columns in a View or in a CTE.
Typing this list is 'cumbersome', so I am looking for a 'template' generating script, which can for example generate scripts. For example for an insert into an Identity table. But also a view of multiple tables where the column_name are made unique.
Is there something like that available?
Ben
In the past I have build a script which can generate almost any column list. This has proven usefull and I am thinking of extending the functionality of this script. But maybe there is something available on the internet which does this allready.
October 9, 2015 at 3:08 am
Not a template, but...
Open Object explorer, navigate to a table/view, Drag the 'columns' folder to your query window.
SQLPrompt does all the column generation for inserts and selects. It's not free, but it's well worth the price in my opinion.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2015 at 3:44 am
I have an SProc that I use for this purpose. It has a short name - one character would do 😉 - and takes partial names for tables etc. If there are multiple hits it provides a list of EXEC commands for all the matching tables. It will default to a table, but also matches Views, Sprocs, etc.
It outputs a list of all the columns in the format that I like them, and includes a comment with the descriptive name for the comment (from SQL's Metadata, plus we have our own Metadata tables for other purposes). It has an option for other flavours of output - e.g. an INSERT statement, @Parameter EXEC list for SProcs, but also to generate a SELECT with a COUNT for all columns and MAX(LEN( for all string datatype columns etc. etc.
SQL is quite happy for strings to not be quoted in certain circumstances so assuming your table & column listing SProc was called "X" then this would be fine to execute it:
X MyPartialTableName
October 9, 2015 at 4:36 am
GilaMonster (10/9/2015)
SQLPrompt does all the column generation for inserts and selects. It's not free, but it's well worth the price in my opinion.
Thanks, I do use this, but this is 'fairly' limited, see the examples below.
Kristen-173977 (10/9/2015)
..... list of EXEC commands for all the matching tables. .....
Good idea, I think I am going to use this in the appropriate cases. A remark that the table does not exist with the EXEC commands as a suggestion. Thanks.
It outputs a list of all the columns in the format that I like them, and includes a comment with the descriptive name for the comment (from SQL's Metadata, plus we have our own Metadata tables for other purposes). It has an option for other flavours of output - e.g. an INSERT statement, @Parameter EXEC list for SProcs, but also to generate a SELECT with a COUNT for all columns and MAX(LEN( for all string datatype columns etc. etc.
SQL is quite happy for strings to not be quoted in certain circumstances so assuming your table & column listing SProc was called "X" then this would be fine to execute it:
X MyPartialTableName
I am interessested in X, is the script for this available?
Ben
Example of some code I use at the moment:
(This is far from complete. Usage is shown not the actual sp).
------------------------------------------------------------------------------------
-- Ben Brugman
-- 20151009
--
-- Examples for generating templates.
------------------------------------------------------------------------------------
-- Prepare tables.
exec sp_drop A_name -- Drops the table when it exists.
exec sp_drop B_tisch -- Drops the table when it exists.
--
-- Create two tables as an example.
--
Create table A_name(ID int identity(1,1),
text varchar(300),
same_name varchar(300),
A_bunch_of_fields_for_A varchar(234))
Create table B_tisch(ID int identity(1,1),
small_name varchar(6),
Btext varchar(300),
same_name varchar(300),
B_bunch_of_fields_for_b varchar(234))
------------------------------------------------------------------------------------
-- Examples of calls. With the output the example generates.
--
-- Help command, here the output is not shown.
--
exec sp_column_list help
--
-- Generates the column list with type comment (DEFAULT) of the table.
--
exec sp_column_list 'A_name'
/*
ID -- 001 int IDENTITY (1, 1)
, text -- 002 varchar(300)
, same_name -- 003 varchar(300)
, A_bunch_of_fields_for_A -- 004 varchar(234)
*/
--
-- Any string can be supplied.
-- (and/or/, in the beginning will be removed)
-- (<column_name> etc. will be replaced)
--
exec sp_column_list 'and x.<column_name> = y.<column_name> -- OK', 'A_name' --
/*
x.ID = y.ID -- OK
and x.text = y.text -- OK
and x.same_name = y.same_name -- OK
and x.A_bunch_of_fields_for_A = y.A_bunch_of_fields_for_A -- OK
*/
--
-- <ordinal_position> <column_name><DATA_TYPE> -- standard replacement strings.
--
exec sp_column_list ',y.<column_name> -- <ordinal_position> <column_name><DATA_TYPE>', 'B_tisch'
/*
y.ID -- 001 ID int IDENTITY (1, 1)
,y.small_name -- 002 small_name varchar(6)
,y.Btext -- 003 Btext varchar(300)
,y.same_name -- 004 same_name varchar(300)
,y.B_bunch_of_fields_for_b -- 005 B_bunch_of_fields_for_b varchar(234)
*/
--
-- An example which reduces the displaylength of fields.
-- Limit the number of characters displayed. (max20 will be replaced with 20 or shorter).
--
print 'SELECT -- columns displayed with a max length of 20'
exec sp_column_list ',CONVERT(VARCHAR(<max20>),<column_name>) as SHORT_<column_name>', 'B_tisch'
print 'FROM B_tisch'
/*
SELECT -- columns displayed with a max length of 20
CONVERT(VARCHAR(20),ID ) as SHORT_ID
,CONVERT(VARCHAR(6),small_name ) as SHORT_small_name
,CONVERT(VARCHAR(20),Btext ) as SHORT_Btext
,CONVERT(VARCHAR(20),same_name ) as SHORT_same_name
,CONVERT(VARCHAR(20),B_bunch_of_fields_for_b ) as SHORT_B_bunch_of_fields_for_b
FROM B_tisch
*/
--
-- Example of creating a view with unique names. (No 'correct' ON clause (yet)).
-- (Sometimes I use this as a starting point for CTE as wel.)
--
exec sp_column_list 'join', 'A_name|B_tisch'
/*
-------------------------------------------------------------------
------------ Generated :2015-10-09T12:04:03.703 -------------------
------------ Tables :A_name|B_tisch
-------------------------------------------------------------------
SELECT
'--' [--]
-- Action_N Leeg 2015-10-09T12:04:03.703
, ---------------------------- A_name
A.ID A_ID
, A.text A_text
, A.same_name A_same_name
, A.A_bunch_of_fields_for_A A_A_bunch_of_fields_for_A
, ---------------------------- B_tisch
B.ID B_ID
, B.small_name B_small_name -- Edit missed a change. Oeps.
, B.Btext B_Btext
, B.same_name B_same_name
, B.B_bunch_of_fields_for_b B_B_bunch_of_fields_for_b
FROM
A_name A
CROSS JOIN B_tisch B
-- SELECT ALL
-- USE CTRL/SHIFT/Q to enter the query editor for further development.
--------------------------------------------------------------------------
*/
exec sp_drop A_name
exec sp_drop B_tisch
October 9, 2015 at 8:12 am
ben.brugman (10/9/2015)
I am interessested in X, is the script for this available?
Sorry, because it, currently, relies on Metadata tables specific to our APPs it won't work standalone.
I should try to find the time to make a generic version though ...
October 12, 2015 at 2:19 am
Kristen-173977 (10/9/2015)
Sorry, because it, currently, relies on Metadata tables specific to our APPs it won't work standalone.I should try to find the time to make a generic version though ...
Maybe some type of cooperation would be possible.
Any takers?
As can be seen I have allready made something, it does work, but is not elegant, not easy to use and extending and maintenance is a problem. But as a single user (and some colleges), the advantages to spend more time on it is limited.
Cooperation within this forum might result in some beautifull and usefull products. (For example the split routine is allready the result of some common effort).
Ben.
Last friday I added an Alias function to the set, this makes it possible to generate a view (or the basis for CTE) where the alias can be choosen and this is used to make the fieldname unique. In the last example A and B not can be choosen aliasses.
October 12, 2015 at 5:27 am
ben.brugman (10/12/2015)
Maybe some type of cooperation would be possible.Any takers?
I'd be up for that 🙂
I suppose the key elements are:
See if matching Table / View / SProc exists
If not , or there are wildcard matches, display a list so user can refine their query
If having displayed the list there is one likely candidate then proceed using that (user can still refine their query from the list if the "guestimate" is wrong.
(That code I already have)
Select meta data about all columns to a #WorkingTable.
That I mostly have. We don't use certain datatypes, and we have our own meta data about all columns / tables which is of more interest to me than the SQL metadata in sys.columns (e.g. we have forced-uppercase datatypes, and so on.)
So I could adapt that. My #WorkingTable contains definitions for the columns - e.g. "VARCHAR(100)" which can then be used in the output phase.
Output section
I don't currently have any templates (but I like that idea :Wow: and would like to add it)
With some work I could probably adapt my code to output some predefined formats. Currently I have:
Column list1 (comma delimited)
PKey Column list (comma delimited)
Column list2 (one column per line/row, with comment for Column Description [from metadata])
[Includes FROM MyTable and ORDER BY PKeyColumnList]
Column list3 - same as previous, one-per-line, but uses ALIAS names based on the metadata column description
DECLARE list for all columns, one per line:
@MyColumn varchar(100), -- Metadata description
(Also useful for just checking what the defined datatype / scale/size/etc. is for the column)
Those are ALL output if I do:
X MyTablePartialName
and then I can cut & paste whichever bit I need.
For a TABLE I also get:
JOINS - JOIN examples for all FKeys like this:
SELECT TOP 10 *
FROMMyTable
LEFT OUTER JOIN dbo.MyOtherTable AS MyAliasName
ON MyAliasName.MyColumn = MyParentColumn
For a VIEW I also get:
VIEW Source code (i.e. sp_helptext MyViewName)
For an SProc I get:
SProc Definition - basically sp_help MySProc which shows parameter definitions
Parameter call list e.g.
, @Param1= @Param1
, @Param2= @Param2
...
I also get a "Logging call" - we concatenate all parameter values together and save them in a Log table. e.g. this:
'@Param1=' + COALESCE(CONVERT(varchar(20), @Param1), 'NULL')
+ ',@Param2=' + COALESCE(''''+@Param2+'''', 'NULL')
+ ...
I doubt that will be of interest to anyone else!!
I have an optional "@Option" parameter which offers (for a table):
@ - e.g.
SELECT
@Col1 = Col1,-- Col1 Metadata description
@Col2 = Col2,-- Col2 Metadata description
...
FROMdbo.MyTable
ORDER BY PKeyColumnList
@@ - similar e.g.
SELECT
@Col1 = [highlight="#ffff11"]@[/highlight]Col1,-- Col1 Metadata description
@Col2 = [highlight="#ffff11"]@[/highlight]Col2,-- Col2 Metadata description
...
FROMdbo.MyTable
ORDER BY PKeyColumnList
@List - similar e.g.
SELECT
@Col1,-- Col1 Metadata description
@Col2,-- Col2 Metadata description
...
FROMdbo.MyTable
ORDER BY PKeyColumnList
(Not intended to be used as a SELECT list, but can be useful as a @Param list)
COUNT normal output for a TABLE but added on the end is:
-- Use this query to produce a column list with a count of the number of rows (where used)
SELECT
[Col1] = SUM(CASE WHEN Col1 IS NULL THEN 0 ELSE 1 END),
[Col2] = SUM(CASE WHEN Col2 IS NULL THEN 0 ELSE 1 END),
...
FROMdbo.MyTable
Similarly for MAXLEN
...
[Col1] = MAX(DATALENGTH(Col1)),
[Col2] = MAX(DATALENGTH(Col2)),
...
INSERT The column name list2, one-per-row, is replaced with:
SELECT
MyGUID= 'GUID',-- GUID [PK]
MyINT= 99999,-- Some description
MyVarchar40= 'xxx40',-- Some description
MyINT2= 99999999999,-- (Uses my metadata for sizing)
MyBIT= bit,-- Some description
...
...
FROMdbo.MyTable
ORDER BY PKeyColumnList
I use it for:
INSERT INTO MyTable(Col1, Col2, ...)
SELECT ...
The "Col1, Col2, ..." is available as the resultset output so I cut&paste that as well.
October 13, 2015 at 9:33 am
Sorry for this late response.
(Last few days I have had to take care of my elderly father who was hospitilized.).
Depending on my work and my father I stil want to make some progress with 'our project'.
We have to establish what we want. Although we have similar wishes we have not jet inventarised what we want/need.
We want to make 'templates' for existing tables.
Templates for anything were a columnlist is required.
(insert/select/update/multiple table templates).
For example for view/CTE where distinct column names are required.
If possible with a simple interface.
If possible as text, but also in a string variable. (For further manipulation).
Available for all databases in an instance.
In your examples I saw some advanced features like the ON clause which was generated.
So can we come up with a common set of requirements ?
And any others who have idea's or want to join in?
Or remarks from others ?
Ben
October 13, 2015 at 11:57 am
ben.brugman (10/13/2015)
We have to establish what we want. Although we have similar wishes we have not jet inventarised what we want/need.
🙂
I don't have much time, so without promising more than I can deliver I reckon all I can do at this stage is deliver from what I already have. That has no templates, as such, but it does generate much of what would be needed for templates (e.g. including data definition such as "varchar(10)")
So I can par back what I already have to something that works without my metabase tables etc. and you/someone would then be welcome to take that further.
But that is only if what I already have is a useful starting point for whatever anyone else might want to add on.
October 14, 2015 at 10:05 pm
GilaMonster (10/9/2015)
Not a template, but...Open Object explorer, navigate to a table/view, Drag the 'columns' folder to your query window.
SQLPrompt does all the column generation for inserts and selects. It's not free, but it's well worth the price in my opinion.
I was thinking the same thing! Great tool!
Dana
October 15, 2015 at 8:25 am
Here is an article I wrote a couple years ago...
http://wvmitchell.blogspot.com/2012/01/sql-list-columns-in-table-updated.html
and the source code is here: http://www.wvmitchell.com/blogger/wm_columns.htm
this only works for tables. I have another version of views if anybody is interested.
October 16, 2015 at 10:32 am
I often use this technique:
IN object explorer, navigate to your table, right click on the column name, then select:
Script Table As> Insert To> (pick your preferred file, window, etc).
This generates the script with all the column names for that table or view.
Sandy
October 20, 2015 at 8:45 am
Everybody thanks for your contribution.
Especially; Kristen and William Mitchell.
Allthough there is a difference is style for all solutions, there is also a large overlap in the different solutions. Clairly there is demand for something like this.
The solutions of Kristen, William and myself al deliver scripts with both similarities and differences. Having something combined would give a powerfull solution, which is not available to this extend in SSMS. I still would like to collaborate on this, because for a single person this is quite a large task, but more importantly, with feedback, the usage and functionality can be improved so that the functionality can be appreciated by more people.
I am still extending my functional set, but still have a number of wishes and a limited amount of time. And as a 'single' user I am not exposed to criticisum from others.
Below the signature some remarks,
All thanks for your contributions to this thread,
Ben
Remarks.
Templates for : Create, Insert, Select, Update, Delete.
Multiple table templates for : View, CTE, Merge.
Supporting Aliasses and identity issues.
Preferable: Generated scripts should be syntactically correct and runable without any changes.
Mutating scripts should be accompanied with something like 'WHERE 1 = 2' to prevent accidental mutations when still composing the script.
Column_names in views and CTE should be distinct. (Using the alias).
October 21, 2015 at 4:38 pm
Here is what I use all the time. Just set the database, schema and table names. Hope it helps.
/* =================================================================================================================================================== */
/* Create column name list that is comma delimited, allowing an optional prefix value (normally the allias name) */
/* =================================================================================================================================================== */
Use MASTER
GO
SET NOCOUNT ON;
--Declare all variables
DECLARE @svn varchar(128)
DECLARE @DBN varchar(100)
DECLARE @cmd varchar(4000)
DECLARE @dbname varchar(300)
DECLARE @schemaname varchar(300)
DECLARE @tablename varchar(300)
DECLARE @schematablename varchar(300)
DECLARE @prefix varchar(300)
DECLARE @suffix varchar(300)
DECLARE @runforsetvariable int
DECLARE @delim varchar(4000)
DECLARE @nn varchar(8)
--For Cursor
DECLARE @de varchar(300)
DECLARE @opint
DECLARE @dtvarchar(20)
DECLARE @ml varchar(20)
/* ============================================================================================= */
--User settable variables
SET @dbname = '<DBName>'
SET @schemaname = '<SchemaName>'
SET @tablename = '<TableName>'
SET @prefix = ''
SET @suffix = ''
SET @runforsetvariable = 1-- if = 1, then creates the code to set each value into a variable
/* ============================================================================================= */
SET @schematablename = '[' + @schemaname + '].[' + @tablename + ']'--ApartmentGuide.dbo.FactWebFormLeads
BEGIN TRY
DROP TABLE #tmptablelist
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #tmptablelist(
[ServerName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[SchemaName] [nvarchar](128) NULL,
[TableName] [sysname] NOT NULL,
[DataElement] [sysname] NULL,
[OrdinalPosition] [int] NULL,
[DataType] [nvarchar](128) NULL,
[MaxLen] [char](10) NOT NULL,
[Prec] [char](10) NOT NULL,
[Scale] [char](10) NOT NULL,
[Identity] [varchar](1) NOT NULL,
[NOT NULL] [varchar](8) NOT NULL,
[References] [nvarchar](258) NOT NULL,
[DefaultValue] [nvarchar](4000) NOT NULL
)
DECLARE myCursor CURSOR FOR
select CONVERT(varchar(300),@@servername) AS ServerName, CONVERT(varchar(300),name) as DBName
FROM sys.databases
WHERE name NOT IN('tempdb','master','msdb','model')-- avoid system databases
AND (state_desc ='ONLINE') -- Avoid Offline Databases
AND (source_database_id Is Null) -- Avoid Database Snapshot
AND name = @dbname
ORDER BY name
OPEN myCursor
-- Loop through all the files for the database
FETCH NEXT FROM myCursor INTO @svn, @DBN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'USE [' + @DBN + ']; INSERT INTO #tmptablelist '
+ 'SELECT
@@Servername AS ServerName,
DB_Name() AS DatabaseName,
TABLE_SCHEMA AS ''SchemaName'',
TABLE_NAME AS ''TableName'',
COLUMN_NAME AS ''DataElement'',
ORDINAL_POSITION AS ''OrdinalPosition'',
DATA_TYPE AS ''DataType'',
ISNULL(CONVERT(varchar(20),CHARACTER_MAXIMUM_LENGTH),'''') AS ''MaxLen'', ISNULL(CONVERT(char(10),NUMERIC_PRECISION),'''') AS ''Prec'', ISNULL(CONVERT(char(10),NUMERIC_SCALE),'''') AS ''Scale'',
CASE WHEN COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1 then ''Y'' ELSE '''' END AS ''Identity'',
CASE IS_NULLABLE WHEN ''YES'' THEN '''' ELSE ''NOT NULL'' END AS ''NOT NULL'',
ISNULL((SELECT top 1 object_name(k.referenced_object_id) + ''('' + col_name(k.referenced_object_id,k.referenced_column_id) + '')'' FROM sys.foreign_key_columns k WHERE object_name(k.parent_object_id) = gg.TABLE_NAME AND col_name(k.parent_object_id,k.parent_column_id) = gg.COLUMN_NAME),'''') AS ''References'',
ISNULL(COLUMN_DEFAULT,'''') AS ''DefaultValue''
FROM information_schema.columns gg
ORDER BY [SchemaName], TABLE_CATALOG, TABLE_NAME, ORDINAL_POSITION'
--PRINT @cmd
EXEC (@cmd)
FETCH NEXT FROM myCursor INTO @svn, @DBN
END
CLOSE myCursor
DEALLOCATE myCursor
/* ============================================================================================= */
--SELECT * FROM #tmptableList ORDER BY ServerName, DatabaseName, SchemaName, TableName, OrdinalPosition
--do this change for the whole table
UPDATE #tmptableList SET DefaultValue = SUBSTRING(DefaultValue,2,LEN(DefaultValue)-2) WHERE DefaultValue <> '' AND (DefaultValue IS NOT NULL)
UPDATE #tmptableList SET Prec='',Scale='' WHERE DataType = 'money' OR DataType = 'smallmoney' OR DataType = 'int' OR DataType = 'bigint' OR DataType = 'tinyint' OR DataType = 'smallint'
UPDATE #tmptableList SET Prec='',Scale='',MaxLen='' WHERE (DataType = 'image') OR (DataType = 'varbinary' AND Prec = 'max')
--pull the values from Prec and Scale over to MaxLen if they apply. That way, don't have to print Prec and Scale
UPDATE #tmptableList SET MaxLen = '(' + CONVERT(varchar(20),LTRIM(RTRIM(Prec))) + ',' + CONVERT(varchar(20),LTRIM(RTRIM(Scale))) + ')', Prec = '', Scale = '' WHERE MaxLen = '' AND Prec <> '' AND Scale <> ''
UPDATE #tmptableList SET MaxLen = 'max' WHERE MaxLen = '-1'
UPDATE #tmptableList SET MaxLen = '(' + LTRIM(RTRIM(MaxLen)) + ')' WHERE DataType = 'char' OR DataType = 'varchar' OR DataType = 'nchar' OR DataType = 'nvarchar'
/* ============================================================================================= */
SET @schematablename = REPLACE(REPLACE(@schematablename,'[',''),']','')-- in case user dragged the table name over from the list and it has the [] in it
/* ============================================================================================= */
if @runforsetvariable = 1
BEGIN
--Print the DECLARE statements for the columns
PRINT '--Create the DECLARE statements for SPROC Input parameters'
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition, DataType, MaxLen, [Not Null] FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt, @ml, @nn
WHILE @@FETCH_STATUS = 0
BEGIN
if @op = 1
PRINT '@' + lower(@de) + ' ' + @dt + @ml + (CASE WHEN @nn = 'NOT NULL' THEN char(9) + char(9) + 'NOT NULL' ELSE '' END)
else
PRINT ',@' + lower(@de) + ' ' + @dt + @ml + (CASE WHEN @nn = 'NOT NULL' THEN char(9) + char(9) + 'NOT NULL' ELSE '' END)
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt, @ml, @nn
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
PRINT ' '
PRINT ' '
/* ============================================================================================================== */
--Print the DECLARE statements for the columns
PRINT '--Create the DECLARE statements for table column variables'
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition, DataType, MaxLen FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt, @ml
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DECLARE @' + lower(@de) + ' ' + @dt + @ml
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt, @ml
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
PRINT ' '
PRINT ' '
/* ============================================================================================================== */
--Print the SET statements for the columns
PRINT '--SELECT statement for setting the table column variables'
PRINT 'SELECT '
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition, DataType, MaxLen FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt, @ml
WHILE @@FETCH_STATUS = 0
BEGIN
if @op = 1
PRINT '@' + lower(@de) + ' = ' + @de
else
PRINT ', @' + lower(@de) + ' = ' + @de
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt, @ml
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
PRINT 'FROM ' + @dbname + '.' + @schemaname + '.' + @tablename
PRINT '--WHERE <somecondition>'
PRINT ' '
PRINT ' '
END
/* ============================================================================================= */
--Print the columns in separated list
PRINT '--Column names to place in cursor'
DECLARE @cursorvals varchar(max)
SET @cursorvals = ''
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op
WHILE @@FETCH_STATUS = 0
BEGIN
--Do Something Here
SET @cursorvals = @cursorvals + '@' + LOWER(@de) + ', '
FETCH NEXT FROM myCursorVariable INTO @de, @op
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
--PRINT @cursorvals
PRINT SUBSTRING(@cursorvals,1,LEN(@cursorvals) -1)
PRINT ' '
PRINT ' '
/* ============================================================================================= */
--Print the columns in separated list
PRINT '--Column names in comma-separated list'
DECLARE @colvals varchar(max)
SET @colvals = ''
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op
WHILE @@FETCH_STATUS = 0
BEGIN
--Do Something Here
SET @colvals = @colvals + @de + ', '
FETCH NEXT FROM myCursorVariable INTO @de, @op
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
--PRINT @cursorvals
PRINT SUBSTRING(@colvals,1,LEN(@colvals) -1)
PRINT ' '
PRINT ' '
/* ============================================================================================= */
--Print the columns in separated list
PRINT '--Column names to place in SELECT statement'
PRINT 'SELECT '
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op
WHILE @@FETCH_STATUS = 0
BEGIN
--Do Something Here
if @op = 1
PRINT @prefix + @de + @suffix /*+ @de*/
else
PRINT ', ' + @prefix + @de + @suffix /*+ @de*/
FETCH NEXT FROM myCursorVariable INTO @de, @op
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
PRINT 'FROM ' + @dbname + '.' + @schemaname + '.' + @tablename
PRINT '--WHERE <somecondition>'
PRINT '--ORDER BY '
PRINT ' '
PRINT ' '
/* ============================================================================================= */
--Print the columns in separated list
PRINT '--Column names to place in COALESCE or MAX statements'
PRINT 'SELECT '
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op
WHILE @@FETCH_STATUS = 0
BEGIN
--Do Something Here
PRINT ', MAX(a.' + @prefix + @de + @suffix + ', b.' + @prefix + @de + @suffix + ') AS ' + @de
FETCH NEXT FROM myCursorVariable INTO @de, @op
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
PRINT 'FROM ' + @dbname + '.' + @schemaname + '.' + @tablename
PRINT '--WHERE <somecondition>'
PRINT '--ORDER BY '
PRINT ' '
PRINT ' '
/* ============================================================================================= */
--Highest value in table
PRINT ' '
PRINT ' '
PRINT '--Highest value in table'
--DECLARE @cursorvals varchar(max)
SET @cursorvals = ''
DECLARE myCursorVariable CURSOR FOR
SELECT DataElement, OrdinalPosition, DataType FROM #tmptablelist WHERE Databasename = @dbname AND SchemaName = @schemaname AND TableName = @tablename ORDER BY OrdinalPosition
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt
WHILE @@FETCH_STATUS = 0
BEGIN
--Do Something Here
--SET @cursorvals = @cursorvals + '@' + LOWER(@de) + ', '
if @op = 1
PRINT '(SELECT MAX(' + @de + ') FROM ' + @dbname + '.' + @schemaname + '.' + @tablename + ' WHERE YMDID BETWEEN 20130101 AND 20150403) AS ' + @de
else
PRINT ',(SELECT MAX(' + @de + ') FROM ' + @dbname + '.' + @schemaname + '.' + @tablename + ' WHERE YMDID BETWEEN 20130101 AND 20150403) AS ' + @de
FETCH NEXT FROM myCursorVariable INTO @de, @op, @dt
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
--PRINT @cursorvals
--PRINT SUBSTRING(@cursorvals,1,LEN(@cursorvals) -1)
PRINT ' '
PRINT ' '
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply