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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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