March 20, 2013 at 6:46 am
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
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