Posts

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
IFNOTEXISTS(SELECT*FROMsys.tables T JOINsys.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 CURSORFAST_FORWARDFOR SELECTCASEWHENPATINDEX('% %',C.name)> 0 THEN'['+ C.name +…

Get Directory Structure using Extended Stored Procedure xp_dirtree

Image
Exec xp_dirtree 'C:\Windows' Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use. Here is the quick script which will insert the data into the temptable and retrieve from the same. CREATE TABLE #TempTable (SubdirectoryVARCHAR(512), Depth INT);INSERT INTO #TempTable (Subdirectory,Depth)EXEC xp_dirtree 'C:\Windows'SELECT Subdirectory, DepthFROM #TempTable;DROP TABLE #TempTable;

List Directory Files using T-SQL xp_cmdShell Stored Procedure

Image
Here is a example code showing how to list directory files in a file folder using sql xp_cmdshell procedure.
CREATE TABLE dirList (
  id int identity(1,1),
  line nvarchar(1000)
)
GO;

INSERT INTO dirList (line) EXEC xp_cmdshell 'dir C:\SQLDatabases'

SELECT * FROM dirList;
WITH CTE AS (
  SELECT
    id,
    SUBSTRING(line,1,17) [date],
    SUBSTRING(line,18,19) sizeordir,
    SUBSTRING(line,37,100) name
  FROM dirList
  WHERE id > (
    SELECT MIN(id) FROM dirList WHERE line LIKE '%<DIR>%..%'
  ) AND id < (SELECT MAX(id) - 2 FROM dirList)
)
SELECT
  id,
  [date],
  isDirectory = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 1 ELSE 0 END,
  isFile = CASE WHEN sizeordir LIKE '%<DIR>%' THEN 0 ELSE 1 END,
  name
FROM cte
The above t-sql script can be used to list files in directory using sql code.
The isDirectory column and isFile column can be used to distinguish files and folders within the target file folder.

By using recursive sql select statements, it is possi…

Additive, Semi-Additive and Non-Additive Measures

Let us begin with the definition of each which will be followed by examples:

1. Additive measures: These are those specific class of fact measures which can be aggreagated across all dimension and their hieracchy

2. Semi-Additive measures: These are those specific class of fact measures which can be aggreagated across all dimension and their hieracchy except the time dimension

3. Non-additive measures: These are those specific class of fact measures which cannot be aggreagated across all/any dimension and their hieracchy

For example:

A) We have sales figures...one may tend to add sales across all quareters to avail the yearly sales..hence this is an example of Additive measure

B) We have stock levels say 1000(qty of Item A) on Monday...I sell 200(qty of Item A) on Tuesday I further sell 300(qty of Item A) on Wednesday...going by basic math On Thursday I should be left with 500(qty of Item A, assuming no inventory has flown in) to obtain current stock level I cannot aggregate the Stock sales…

SQL Server 2008 Integration Services Tasks

Image
SQL Server 2008 Integration Services Tasks SQL Server Integration Services (SSIS) is a Business Intelligence tool used by database developers or administrators to perform extract transform & load (ETL) operations. SSIS has come long way since the early days of Data Transformation Services (DTS) which was initially introduced by Microsoft in SQL Server 7.0. Most database developers and administrators had their first interaction with DTS when using the Import and Export Wizard. The primary objective of DTS was to transform data from an OLEDB data sources to another destination. In SQL Server 2008 Integration Services, there are around 28 different control flow tasks and 11 Maintenance Plan Tasks. This article is the first in a series that will cover all the Control Flow Tasks and Maintenance Plan Tasks in SQL Server 2008 Integration Services. Beginning with an overview of the Control Flow Tasks and Maintenance Plans in SSIS 2008.
Overview of Control Flow Tasks in SSIS
The Control Fl…

Use of Script Task to create SQL table using VB.Net in SSIS

The Below Code will be useful in the cases when we need to deal with flat files, wherein the file structure may be dynamic and we are dealing with multiple files each one with a different schema definition.
The approach will be here is to loop through each file and then read the first line of each file and read the column names from the file and then dynamically create the table and then load the data.
The below code is used to read the column information from the file and then generate a SQL Command which will create the table in the database.
Here it goes - 
Code to dynamically create a table using the Input File Data –
PublicSub Main() Dim objReader AsNew StreamReader(Dts.Variables("SRCDATAFILE").Value.ToString) Dim sLine AsString = "" Dim arrText AsNew ArrayList()         sLine = objReader.ReadLine() 'MsgBox(sLine)         sLine = sLine.Replace(ControlChars.Tab, "|") 'MsgBox(sLine)         sLine = Trim(sLine.Replace(" |", "|").Repla…

Scripting Indexes in SQL 2000

DECLARE IndexCursor CURSORFOR SELECTOBJECT_NAME(SI.ID), SI.ID, SI.Name, SI.indid FROM dbo.sysindexes SI LEFTJOININFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME ANDOBJECT_NAME(SI.ID)= TC.TABLE_NAME WHERE TC.CONSTRAINT_NAME ISNULL ANDOBJECTPROPERTY(SI.ID,'IsUserTable')= 1 ANDINDEXPROPERTY( SI.[id], SI.[name],'IsStatistics')= 0 ANDOBJECT_NAME(SI.ID)in ( 'Table1', 'Table2' ) ORDERBYOBJECT_NAME(SI.ID), SI.indid

DECLARE @IxTable varchar(50) DECLARE @IxTableID INT DECLARE @IxName varchar(50) DECLARE @IxID INT
-- Loop through all indexes OPEN IndexCursor FETCHNEXTFROM IndexCursor INTO @IxTable, @IxTableID, @IxName, @IxID WHILE (@@FETCH_STATUS= 0) BEGIN DECLARE @IXSQL NVARCHAR(4000)