Search This Blog

Ret the table structure

DECLARE
@TableName varchar(1000)
SET @TableName = ''

DECLARE TableCursor CURSOR FOR
SELECT [Name] AS [Table Name]
FROM sysObjects
WHERE xType = 'U'
ORDER BY [Name]
OPEN TableCursor

FETCH NEXT FROM TableCursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT DISTINCT
lower(Obj.Name) AS TableName
,Col.Name AS ColumnName
,St.Name AS DataType
,Col.Length AS FieldSize
,IsPk =
CASE
WHEN Left(ISC.constraint_name,2) ='PK'
THEN 'Y'
ELSE '-'
END
,IsAutoNum =
CASE
WHEN Col.Autoval Is Not Null
THEN 'Y'
ELSE '-'
END
FROM sysObjects Obj

INNER JOIN sysColumns Col
ON Obj.ID = Col.ID
INNER JOIN systypes ST
ON ST.Xtype = Col.Xtype
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ISC
ON TABLE_NAME = @TableName AND Col.[Name] = ISC.COLUMN_NAME
WHERE obj.xtype = 'U' AND obj.[Name] = @TableName

DECLARE @SQL varchar(200)
SET @SQL = 'SELECT ''' + @TableName +' Count = '' + cast(Count(*) as varchar) AS '''+@TableName+' COUNT'' FROM ' + @TableName
EXEC(@SQL)
FETCH NEXT FROM TableCursor
INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

1 comment:

Alwin Co Daan said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.