SQK Server table definition 1

[kel1981b]

Is there a way to export the layout definitions of each table of particular database to some type file that I could then import into an Excel sheet?

 

[Hubajube]

Here's one to show the tables & colums:

[tt]
SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS ColName,
dbo.syscolumns.colid AS ColOrder, dbo.systypes.name,
dbo.syscolumns.length, dbo.syscolumns.prec, dbo.syscolumns.isnullable
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id LEFT OUTER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE OBJECTPROPERTY(OBJECT_ID(dbo.sysobjects.name), 'ISTABLE') = 1
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid
[/tt]

and here's one for tables & indexes:

[tt]
SELECT dbo.sysobjects.name AS TableName, dbo.sysindexes.name AS IndexName,
dbo.sysindexkeys.keyno, dbo.syscolumns.name,
CASE WHEN dbo.sysindexes.status & 2 = 2 THEN 'Yes'
ELSE 'No'
END AS IsUnique,
CASE WHEN dbo.sysindexes.status & 16 = 16 THEN 'Yes'
ELSE 'No'
END AS IsClustered
FROM dbo.sysindexes INNER JOIN
dbo.sysobjects ON dbo.sysindexes.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexkeys ON dbo.sysindexes.id = dbo.sysindexkeys.id
AND dbo.sysindexes.indid = dbo.sysindexkeys.indid INNER JOIN
dbo.syscolumns ON dbo.sysindexkeys.colid = dbo.syscolumns.colid
AND dbo.sysindexkeys.id = dbo.syscolumns.id
WHERE (LEFT(dbo.sysindexes.name, 3) <> '_WA'
AND LEFT(dbo.sysindexes.name, 3) <> 'hin')
AND (dbo.sysobjects.xtype <> 'S')
ORDER BY dbo.sysobjects.name, dbo.sysindexes.indid, dbo.sysindexkeys.keyno
[/tt]

 

[Hubajube]

Here's one to show the tables & colums:

[tt]
SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS ColName,
dbo.syscolumns.colid AS ColOrder, dbo.systypes.name,
dbo.syscolumns.length, dbo.syscolumns.prec, dbo.syscolumns.isnullable
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id LEFT OUTER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtypea
WHERE OBJECTPROPERTY(OBJECT_ID(dbo.sysobjects.name), 'ISTABLE') = 1
ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid
[/tt]

and here's one for tables & indexes:

[tt]
SELECT dbo.sysobjects.name AS TableName, dbo.sysindexes.name AS IndexName,
dbo.sysindexkeys.keyno, dbo.syscolumns.name,
CASE WHEN dbo.sysindexes.status & 2 = 2 THEN 'Yes'
ELSE 'No'
END AS IsUnique,
CASE WHEN dbo.sysindexes.status & 16 = 16 THEN 'Yes'
ELSE 'No'
END AS IsClustered
FROM dbo.sysindexes INNER JOIN
dbo.sysobjects ON dbo.sysindexes.id = dbo.sysobjects.id INNER JOIN
dbo.sysindexkeys ON dbo.sysindexes.id = dbo.sysindexkeys.id
AND dbo.sysindexes.indid = dbo.sysindexkeys.indid INNER JOIN
dbo.syscolumns ON dbo.sysindexkeys.colid = dbo.syscolumns.colid
AND dbo.sysindexkeys.id = dbo.syscolumns.id
WHERE (LEFT(dbo.sysindexes.name, 3) <> '_WA'
AND LEFT(dbo.sysindexes.name, 3) <> 'hin')
AND (dbo.sysobjects.xtype <> 'S')
ORDER BY dbo.sysobjects.name, dbo.sysindexes.indid, dbo.sysindexkeys.keyno
[/tt]

 

[kel1981b]

Thank you Hubajube. I'll try it.

 

[kel1981b]

Thanks a lot! It dies work! You help me a lot!