July 16, 2010 at 3:10 am
Hi, I have to list all the tables in my database, plus the column names in each, and how they all link together, for a data mapping exercise. I can get the table names out but is there an easy way to get all tables plus their column names in one fell swoop? Preferably listing the properties of each column? Hope someone out there can help, don't want to be doing c3,000 tables manually! Newbie so need it simple, too! Many thanks, Jules.
July 16, 2010 at 4:50 am
try This.
SELECTo.Name as TableName,
c.Name as ColumnName,
t.name as DataType,
t.length as [DataLength],
c.collation
FROM sysobjects o
JOIN syscolumns c
ON o.id = c.id
JOIN systypes t
ON c.xtype = t.xtype
WHERE o.xtype = 'u'
ORDER By o.name
MCITP SQL 2005, MCSA SQL 2012
July 16, 2010 at 7:58 am
Wow that's great thanks very much. Don't support there's an extra bit that will give a count of all rows in each table? (Pushing it I know!).
Cheers, Jules
July 16, 2010 at 8:09 am
You can add a join to sysindexes and filter for clustered index or heap (indid in (0,1)
July 16, 2010 at 8:42 am
That sounds marvellous but could you add it into the original script please? my sql knowledge is about 0.1% so didn't really understand!
Cheers, Jules
July 16, 2010 at 9:11 am
SELECT o.Name as TableName,
i.rowcnt Row_count,
c.Name as ColumnName,
t.name as DataType,
t.length as [DataLength],
c.collation
FROM sysobjects o
JOIN syscolumns c
ON o.id = c.id
JOIN systypes t
ON c.xtype = t.xtype
JOIN Sysindexes i
ON o.id=i.id
WHERE o.xtype = 'u'
and i.indid in (0,1)
ORDER By o.name
July 16, 2010 at 9:14 am
Fan-blimmin-tastic thanks very very much. Saved me much time. Now all I gotta do is map away! HAGW Jules
February 7, 2019 at 7:54 am
Does anyone know how to add schema to the query?
February 7, 2019 at 8:25 am
deb.pabst - Thursday, February 7, 2019 7:54 AMDoes anyone know how to add schema to the query?
With the idea of teaching someone to fish, the easiest way is to use a function to do so. Lookup the Object_Schema_Name() function and lookup sys.Objects to determine which column to apply it to.
Also remember that this is an old post and that sysobjects and sysindexes have been deprecated. If you look those up, you'll find out what the replacements are for those. You might also want to have a peek at sys.columns and INFORMATION_SCHEMA.COLUMNS.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2019 at 8:35 am
Jeff,
Thanks so much, I kept googling and found the last option you mentioned. I also appreciate the teaching to fish sentiment as it is my favorite!
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
February 7, 2019 at 8:55 am
If you wanted to work on updating the query to use the current catalog views, there is a document showing which old system tables map to which current views.
Mapping System Tables to System Views
A few things would have to change (such as the old tables used id and the new views usually use object_id) but rewriting wouldn't be too hard and would help understanding more about the new views.
Sue
February 7, 2019 at 9:15 am
Heh... and Sue also teaches how to unsnarl the reel! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2019 at 11:33 am
One thing I don't see anyone addressing is DRI (declared referential integrity)... AKA Foreign Key Constraints.
This information is crucial (at least IMHO) if mapping relationships between tables is part of the goal.
While I 100% support the idea that teaching one to fish is better than giving one a fish, putting all of this together can be a daunting task, even for seasoned DBAs and developers.
Sooo... With that said, I hope you use the following code as a staring point not the finish line...
SELECT
o.object_id,
table_name = CONCAT(SCHEMA_NAME(o.schema_id), N'.', o.name),
c.column_id,
column_name = c.name,
data_type = CONCAT(t.name, psl.precision_scale_len, ni.identity_info, ni.nullability),
fkx.fk_constraint
FROM
sys.objects o WITH (NOLOCK)
JOIN sys.columns c WITH (NOLOCK)
ON o.object_id = c.object_id
JOIN AlignDev.sys.types t WITH (NOLOCK)
ON c.user_type_id = t.user_type_id
CROSS APPLY ( VALUES (CASE
WHEN c.user_type_id IN (34,35,36,40,48,52,56,58,59,60,61,62,98,99,104,122,127,128,129,130,189,241,256) THEN N''
WHEN c.user_type_id IN (106,108) THEN N'(' + CONVERT(NVARCHAR(10), c.precision) + ',' + CONVERT(NVARCHAR(10), c.scale) + N')'
WHEN c.user_type_id IN (41,42,43) THEN N'(' + CONVERT(NVARCHAR(10), c.scale) + N')'
WHEN c.user_type_id IN (165,167,173,175) THEN N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CONVERT(NVARCHAR(10), c.max_length) END + N')'
WHEN c.user_type_id IN (231,239) THEN N'(' + CASE WHEN c.max_length = -1 THEN N'max' ELSE CONVERT(NVARCHAR(10), c.max_length / 2) END + N')'
END) ) psl (precision_scale_len)
CROSS APPLY ( VALUES (
CASE WHEN c.is_nullable = 1 THEN N' null' ELSE N' not null' END,
CASE WHEN c.is_identity = 0 THEN N'' ELSE (SELECT CONCAT(N' identity(', CONVERT(INT, ic.seed_value), N',', CONVERT(INT, ic.increment_value), N')') FROM AlignDev.sys.identity_columns ic WHERE c.object_id = ic.object_id AND c.column_id = ic.column_id) END
) ) ni (nullability, identity_info)
OUTER APPLY (
SELECT
fk_constraint = CONCAT(N'CONSTRAINT ', fk.name,
N' FOREIGN KEY REFFERENCES ', SCHEMA_NAME(CONVERT(INT, OBJECTPROPERTYEX(fkc.referenced_object_id, 'SchemaId'))), N'.', OBJECT_NAME(fkc.referenced_object_id), N' (', rc.name, N')',
N' {ON UPDATE: ', fk.update_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, N'} {ON DELETE: ', fk.delete_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, N'} {IS TRUSTED: ', CASE WHEN fk.is_not_trusted = 1 THEN N'NO' ELSE N'YES' END, N'}'
)
FROM
sys.foreign_key_columns fkc WITH (NOLOCK)
JOIN sys.foreign_keys fk WITH (NOLOCK)
ON fkc.constraint_object_id = fk.object_id
JOIN sys.columns rc WITH (NOLOCK)
ON fkc.referenced_object_id = rc.object_id
AND fkc.referenced_column_id = rc.column_id
WHERE
c.object_id = fkc.parent_object_id
AND c.column_id = fkc.parent_column_id
AND fk.is_disabled = 0
) fkx
WHERE
o.type = 'U';
February 7, 2019 at 12:09 pm
Jeff Moden - Thursday, February 7, 2019 9:15 AMHeh... and Sue also teaches how to unsnarl the reel! 😀
As long as I don't have to put bait on the hook....the only part of fishing I dislike.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply