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

No comments: