Ravendra's Blog

February 10, 2010

SQL Query to find all column names in a table

Filed under: Uncategorized — Ravendra Mishra @ 12:19 pm

 

declare @SearchStr as
varchar(256)

set @SearchStr =
‘System_’

 

CREATE
TABLE #Results
(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

 

    SET
NOCOUNT
ON

 

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

    SET @TableName =

    SET @SearchStr2 =
QUOTENAME(‘%’
+ @SearchStr +
‘%’,””)

 

    WHILE @TableName IS
NOT
NULL

    BEGIN

        SET @ColumnName =

        SET @TableName =

        (

            SELECT
MIN(QUOTENAME(TABLE_SCHEMA)
+
‘.’
+
QUOTENAME(TABLE_NAME))

            FROM     INFORMATION_SCHEMA.TABLES

            WHERE         TABLE_TYPE =
‘BASE TABLE’

                AND    QUOTENAME(TABLE_SCHEMA)
+
‘.’
+
QUOTENAME(TABLE_NAME)
> @TableName

                AND    OBJECTPROPERTY(

                        OBJECT_ID(

                            QUOTENAME(TABLE_SCHEMA)
+
‘.’
+
QUOTENAME(TABLE_NAME)

                             ),
‘IsMSShipped’

                         )
= 0

        )

 

        WHILE (@TableName IS
NOT
NULL)
AND
(@ColumnName IS
NOT
NULL)

        BEGIN

            SET @ColumnName =

            (

                SELECT
MIN(QUOTENAME(COLUMN_NAME))

                FROM     INFORMATION_SCHEMA.COLUMNS

                WHERE         TABLE_SCHEMA    =
PARSENAME(@TableName, 2)

                    AND    TABLE_NAME    =
PARSENAME(@TableName, 1)

                    AND    DATA_TYPE IN
(‘char’,
‘varchar’,
‘nchar’,
‘nvarchar’)

                    AND    QUOTENAME(COLUMN_NAME)
> @ColumnName

            )

    
 

            IF @ColumnName IS
NOT
NULL

            BEGIN

                INSERT
INTO #Results

                EXEC

                (

                    ‘SELECT ”’
+ @TableName +
‘.’
+ @ColumnName +
”’, LEFT(‘
+ @ColumnName +
‘, 3630)

                    FROM ‘
+ @TableName +
‘ (NOLOCK) ‘

                    –+ ‘ WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2

                )

            END

        END    

    END

 

    SELECT
distinct (ColumnName)
FROM #Results order
by ColumnName–where columnName like ‘%Words%’

drop
table #Results

Advertisements

February 2, 2010

SSRS Report on TFS 2010 Portal


Sometimes default reports which comes out of the box with process templates are not enough and custom reports are required. These reports can be put together on SharePoint team portal to create a dashboard or part of existing pages/ dashboards. If using SSRS on SharePoint integrated mode than rdl files of these reports can be copied directly on document libraries. Sometimes SSRS is not running on SharePoint integrated mode specially when SSRS is deployed in different box then SharePoint box. In such cases using Web page view web part can be quite useful.

Let’s say there is project collection called “PCollection” contains a project called “PProject” contains a report called “PReport” then URL for this report on page viewer web part will be something like

/PCollection/PProject/_layouts/tfsredirect.aspx?IsReport=1&ReportName=PReport

If report is located within folders let’s say “Dashboards” then URL will be

/PCollection/PProject/_layouts/tfsredirect.aspx?IsReport=1&ReportName=Dashboards/PReport

If report name of folder name contains multiple words then space between these words should be replaced by ‘+’ symbol. For example in above examples folder name is “Project dashboards” and report name is “Burn up report” then URL will be something like

/PCollection/PProject/_layouts/tfsredirect.aspx?IsReport=1&ReportName=Project+dashboards/Burn+up+report

Create a free website or blog at WordPress.com.