database table search
-
Hi cyber-friends –
I need your help! We are undergoing a project to have all the school names standardized; we want them to be the same every place you see them (that’s not currently the case). We have numerous databases with many, many tables. Does anyone out there have a script that can list the database name, the table, the school name field and the field length for each table? Currently, there are inconsistencies in field sizes between applications tables.
Any ideas will be greatly appreciated 😉
-
Are the table names and field names consistent for the different databases? Are the inconsistencies limited to the field sizes and field values?
-
This query will show you the information for each databases where there is a table with column name ColName.
sp_msforeachdb 'select table_catalog, table_schema, table_name, column_name, data_type, character_maximum_length
from ?.INFORMATION_SCHEMA.COLUMNS
where column_name = ''ColName'' '
-
mdaniel:
When I run your query in Query Analyzer against a MS SQL Server 2000 database, the results show a bunch of empty rows that display the table_catalog, table_schema, table_name, column_name, and data_type column names with no data, along with rows that have the column names with data.
How do I eliminate the empty rows from my display? Thanks for your help.
Sundog
-
The table names, field names and length all differ!
-
I assume you know the column type is char/varchar/nchar/nvarchar. If not, remove the check for "%char%"; if you know it's varchar, naturally you can change it to "%varchar%".
For one db:
SELECT --LEFT(DB_NAME(), 32) AS 'DbName',
LEFT(OBJECT_NAME(c.id), 28) AS 'TableName',
LEFT(c.name, 26) AS 'ColumnName',
CAST(c.colid AS TINYINT) AS 'Col#',
UPPER(LEFT(t.name, 13)) AS 'Type',
CASE WHEN t.name LIKE '%char%' THEN STR(c.length, 5, 0)
WHEN t.name IN ('DECIMAL', 'NUMERIC') THEN STR(c.prec, 5, 0)
ELSE '' END AS 'Length',
CASE WHEN t.name IN ('DECIMAL', 'NUMERIC') THEN STR(c.scale, 3, 0) ELSE '' END AS '#Dec '
FROM syscolumns c WITH (NOLOCK)
INNER JOIN systypes t WITH (NOLOCK) ON c.xusertype = t.xusertype
INNER JOIN sysobjects o WITH (NOLOCK) ON c.id = o.id
WHERE
--o.name IN (N'tablename') --specific table(s)
o.xtype = 'U' AND o.name NOT LIKE 'dt%' --to see all user tables
AND t.name LIKE '%char%'
ORDER BY [TableName], [Col#]
For all dbs:
IF OBJECT_ID('tempdb.dbo.#colsInfo') IS NOT NULL
DROP TABLE #colsInfo
CREATE TABLE #colsInfo (
dbName VARCHAR(100),
tableName VARCHAR(100),
columnName VARCHAR(100),
col# TINYINT,
type VARCHAR(30),
length SMALLINT,
#dec TINYINT
)
EXEC sp_MSForEachDb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''subscription'', ''tempdb'')
BEGIN
INSERT INTO #colsInfo
SELECT LEFT(''?'', 32) AS ''DbName'',
LEFT(o.name, 28) AS ''TableName'',
LEFT(c.name, 26) AS ''ColumnName'',
CAST(c.colid AS TINYINT) AS ''Col#'',
UPPER(LEFT(t.name, 13)) AS ''Type'',
CASE WHEN t.name LIKE ''%char%'' THEN c.length
WHEN t.name IN (''DECIMAL'', ''NUMERIC'') THEN c.prec
ELSE 0 END AS ''Length'',
CASE WHEN t.name IN (''DECIMAL'', ''NUMERIC'') THEN c.scale ELSE 0 END AS ''#Dec ''
FROM [?].dbo.syscolumns c WITH (NOLOCK)
INNER JOIN [?].dbo.sysobjects o WITH (NOLOCK) ON c.id = o.id
INNER JOIN [?].dbo.systypes t WITH (NOLOCK) ON c.xusertype = t.xusertype
WHERE
-- o.name IN (N''tablename'') --specific table(s)
o.xtype = ''U'' AND o.name NOT LIKE ''dt%'' --to see all user tables
AND t.name LIKE ''%char%''
ORDER BY DbName, TableName, Col#
END
'
SELECT LEFT(dbName, 32) AS 'DbName',
LEFT(tableName, 28) AS 'TableName',
LEFT(columnName, 26) AS 'ColumnName',
Col#,
UPPER(LEFT(type, 13)) AS 'Type',
CASE WHEN length = 0 THEN '' ELSE STR(length, 5, 0) END AS 'Length',
CASE WHEN #dec = 0 THEN '' ELSE STR(#dec, 3, 0) END AS '#Dec '
FROM #colsInfo
ORDER BY DbName, TableName, Col#
DROP TABLE #colsInfo
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Hi Scott,
I tried the code 'for one db' without the '%char%', because the fields vary, ran the code but received an 'Arithmetic overflow error for data type tinyint, value = 256.'
Any ideas...thanks.
-
Hi Mdaniel,
Do I need to replace any info on the query above or can I run it just as is??? (I'm still a rookie at this;-) thanks.
-
D'OH, sorry, change the TINYINTs to SMALLINTs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
The change worked;-) Can I extend a question for the script with all the databases....
If I want to get a list of all the tables that have the word school as part of their name, will I have to make a change in the script??
Thanks in advance...
-
Yes, you'll have to change the query for that.
To get tables with '%school%' in them:
1) AND must match all the other criteria, do this:
WHERE o.name LIKE N'%school%'
AND o.xtype = ''U'' AND o.name NOT LIKE ''dt%''
AND t.name LIKE ''%char%''
2) OR all with '%school%' whether they match or not, plus all those that match, do this:
WHERE o.name LIKE N'%school%'
OR (o.xtype = ''U'' AND o.name NOT LIKE ''dt%''
AND t.name LIKE ''%char%'')
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Scott, that worked like a charm; thank you so much
'till the next challenge!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply