List of Columns of a Table in CSV Format

DECLARE @TABLE_NAME VARCHAR(128)
DECLARE @SCHEMA_NAME VARCHAR(128)

-----------------------------------------------------------------------
-- *** User Customisation

-- Set up the name of the table here :
SET @TABLE_NAME = 'Address'
-- Set up the name of the schema here, or just leave set to 'dbo' :
SET @SCHEMA_NAME = 'dbo'

-----------------------------------------------------------------------

DECLARE @vvc_ColumnName VARCHAR(128)
DECLARE @vvc_ColumnList VARCHAR(MAX)

IF @SCHEMA_NAME =''
  BEGIN
  PRINT 'Error : No schema defined!'
  RETURN
  END

IF NOT EXISTS (SELECT * FROM sys.tables T JOIN sys.schemas S
          ON T.schema_id=S.schema_id
          WHERE T.Name=@TABLE_NAME AND S.name=@SCHEMA_NAME)
  BEGIN
  PRINT 'Error : The table '''+@TABLE_NAME+''' in schema '''+
        @SCHEMA_NAME+''' does not exist in this database!'
  RETURN
  END

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT   CASE WHEN PATINDEX('% %',C.name) > 0
         THEN '['+ C.name +']'
         ELSE C.name
         END
FROM     sys.columns C
JOIN     sys.tables T
ON       C.object_id  = T.object_id
JOIN     sys.schemas S
ON       S.schema_id  = T.schema_id
WHERE    T.name    = @TABLE_NAME
AND      S.name    = @SCHEMA_NAME
ORDER BY column_id


SET @vvc_ColumnList=''

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

WHILE @@FETCH_STATUS=0
  BEGIN
  SET @vvc_ColumnList = @vvc_ColumnList + @vvc_ColumnName

  -- get the details of the next column
  FETCH NEXT FROM TableCursor INTO @vvc_ColumnName

  -- add a comma if we are not at the end of the row
  IF @@FETCH_STATUS=0
    SET @vvc_ColumnList = @vvc_ColumnList + ','
  END

CLOSE TableCursor
DEALLOCATE TableCursor

PRINT 'Here is the comma separated list of column names :'
PRINT '--------------------------------------------------'

PRINT @vvc_ColumnList

Comments

Post a Comment

Popular posts from this blog

Deleting Duplicate Data From SQL Server Table

Duplicate data is a very well known and frequently occurring issue in any DBMS and dealing with deletion of duplicate data is something not that easy for one specially one who is dealing with crucial and huge data. There are 2 scenarios which can come while deleting duplicate data - 1. when we have an identity column in our table containing duplicates. The problem becomes much easier here and we can just keep the data with MIN or MAX keys removing all other. --Create New Table create table Person ( ID int identity ( 1 , 1 ), Name varchar ( 30 ), age int ) --Insert Data into Table with multiple Duplicate entries insert into Person ( Name , age ) values ( 'Ashish' , 24 ) insert into Person ( Name , age ) values ( 'Ashish' , 24 ) insert into Person ( Name , age ) values ( 'Gaurav' , 26 ) insert into Person ( Name , age ) values ( 'Nayan' , 25 ) insert into Person ( Name , age ) values ( 'Nayan...
Read more

SSIS vs. T-SQL

This is a common question which comes often before any developer to choose between the two as to follow which path to choose as SSIS or T-SQL for developing ETL solutions. well i also got similar scenario some time back and was in a dilemma to decide this. on a big picture it's always better to go with conventional way i.e. using T-SQL and not to go with the tool. But on doing some more reading and research over many blogs and forums and also the development phase of my work over some 2 and a half odd years. I found some good reasons and explanations over this as not to always stick to a particular approach and do proper analysis of the requirements to take a decision. Below are some of the key points which i was able to collect regarding this. 1.  If the data does need to be persisted temporarily it can be to a raw file in SSIS whereas T-SQL requires temporary tables for which there may be a management/security overhead. 2.  SSIS have a very good visual rep...
Read more

Excel Based Reporting

Hello Everyone, so today my focus is on to give insights about how can we deal with scenarios where the client wants us to help them with Excel based reporting. We will be doing this using SSIS as we will need automation of the report on a daily basis. Technology Items used - 1. SSIS 2. Excel files (format files) 3. Macro codes 4. Conditional formatting. 5. SSIS 6. C#.Net / VB.Net Script Components Step-1:  - We will be writing a few macro codes to do the same as we will need the numeric columns to be in the numeric while we insert the data from SSIS it will automatically convert it to Text. Macro-1 - Code to Convert Text to Integer. Sub Function_Name() sheetlist = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7") For i = LBound (sheetlist) To UBound (sheetlist) Worksheets(sheetlist(i)).Activate ' Your Code Goes Here Range("F:F").Select 'specify the r...
Read more