Post reply

How to get output of select statement stored as a column value in select statement

  • March 20, 2013 at 6:46 am

    #99809

    Hi,

    I am working on one required to list all the columns in entire tables in all databases on particular server having varchar(8000) data type along with column containing present actual data length in each columns listed.

    DROP TABLE #temp_Table

    CREATE TABLE #temp_Table (

    DBName sysname

    ,Table_Name VARCHAR(100)

    ,Column_Name VARCHAR(100) collate database_default

    ,Data_Type VARCHAR(50) collate database_default

    ,Max_Limit INT

    ,Statement NVARCHAR(800) collate database_default

    )

    DECLARE AllDatabases CURSOR FOR

    SELECT name FROM master.dbo.sysdatabases WHERE dbid > 4

    OPEN AllDatabases

    DECLARE @DBNameVar NVARCHAR(128),@Statement NVARCHAR(800)

    DECLARE @Statement2 NVARCHAR(300)

    FETCH NEXT FROM AllDatabases INTO @DBNameVar

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    set nocount on

    --Drop table #ActualValue

    --PRINT N'CHECKING DATABASE ' + @DBNameVar

    SET @Statement = N'USE ' + @DBNameVar + CHAR(13)

    + N'select ''' + @DBNameVar + ''' as DBNAME, C.TABLE_NAME,C.COLUMN_NAME,C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH AS MAX_LIMIT,

    ''Select MAX(DATALENGTH(['' + C.COLUMN_NAME + ''])) FROM [' + @DBNameVar + ']..['' + C.TABLE_NAME + '']'' AS ACTUAL_VALUE

    FROM INFORMATION_SCHEMA.TABLES T

    INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME

    WHERE T.TABLE_TYPE=''BASE TABLE'' and C.DATA_TYPE=''varchar'' AND C.CHARACTER_MAXIMUM_LENGTH=8000'

    INSERT INTO #temp_Table EXEC sp_executesql @Statement

    --PRINT @Statement

    PRINT CHAR(13) + CHAR(13)

    FETCH NEXT FROM AllDatabases INTO @DBNameVar

    END

    CLOSE AllDatabases

    DEALLOCATE AllDatabases

    select * from #temp_table

    Currently from above query I am able to get data in below format having column containing select statement to get actual max column value

    DBName Table_Name Column_Name Data_Type Max_Limit Statement

    Select MAX(DATALENGTH([featurecurrent])) FROM [s60302app]..[interface_feature]

    I want the Output should be in below format

    DBName Table_Name Column_Name Data_Type Max_Limit Actual_Limit

    TestDB TableA Name varchar 8000 5000

    [font="Times New Roman"]Kapil Swamy[/font]

  • March 22, 2013 at 1:43 am

    #1599677

    It seems nobody is well versed with t-sql in this forum

    [font="Times New Roman"]Kapil Swamy[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply