sqlserver中metadata的查询



use Test

--iteration1: check all the table contain key words
SELECT c.COLUMN_NAME, c.table_schema,c.TABLE_NAME, c.DATA_TYPE+case when c.DATA_TYPE='varchar' then '('+convert(varchar,c.CHARACTER_MAXIMUM_LENGTH)+')' when c.DATA_TYPE='nvarchar' then '('+convert(varchar,c.CHARACTER_MAXIMUM_LENGTH)+')'  when c.DATA_TYPE='numeric' then '('+convert(varchar,c.NUMERIC_PRECISION)+','+convert(varchar,c.NUMERIC_SCALE)+')' else '' end,c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
left join sys.objects o
on c.TABLE_NAME=object_name(o.object_id)
WHERE c.COLUMN_NAME LIKE '%OrderNbr%'
and o.type='U'
order by c.COLUMN_NAME,c.TABLE_NAME

--check the data for all the varchar column, mark it if it contain non-numeric character
select  C1 from T1
WHERE C1 like '%[^0-9]%'

--iteration2: check all the dependency
--check all the type we need to consider
select max(object_name(object_id) )
,max(OBJECT_DEFINITION(object_id) )
,type
from sys.objects 
group by type

--Function:  in('TF','FN','FT','FS','IT')
--procedure: in('P','PC')
--view: in ('V')
select
object_name(object_id)
,schema_name(schema_id)  sch_name
,type
,OBJECT_DEFINITION(object_id)
from sys.objects
where OBJECT_DEFINITION(object_id) LIKE '%CustServOrderLines%'  --column name
and OBJECT_DEFINITION(object_id) LIKE '%OrderNumber%'    --table or view name
and object_name(object_id) not like '%NOT_VALID%'
--and type in ('P','PC','V','TF','FN','FT','FS','IT')
order by 1

--check the job Script
USE [msdb]
GO
SELECT --j.job_id,
       s.srvname,
       j.name,
       js.step_id,
    js.step_name,
       js.command,
       j.enabled
FROM   dbo.sysjobs j
JOIN   dbo.sysjobsteps js
       ON     js.job_id = j.job_id
JOIN   master.dbo.sysservers s
       ON     s.srvid = j.originating_server_id
where js.command LIKE '%OrderNumber%'  --column name
and  js.command LIKE '%CustServOrderLines%'    --table or view name
--and js.command LIKE '%Test%'    --database name

--iteration3: check all the views which contain key words
SELECT c.COLUMN_NAME, c.table_schema,c.TABLE_NAME, c.DATA_TYPE+case when c.DATA_TYPE='varchar' then '('+convert(varchar,c.CHARACTER_MAXIMUM_LENGTH)+')' when c.DATA_TYPE='nvarchar' then '('+convert(varchar,c.CHARACTER_MAXIMUM_LENGTH)+')'  when c.DATA_TYPE='numeric' then '('+convert(varchar,c.NUMERIC_PRECISION)+','+convert(varchar,c.NUMERIC_SCALE)+')' else '' end,c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
left join sys.objects o
on c.TABLE_NAME=object_name(o.object_id)
WHERE c.COLUMN_NAME LIKE '%ORDERNUMBER%'
and o.type='V'
order by c.COLUMN_NAME,c.TABLE_NAME 

--check the column type of a object
SELECT c.COLUMN_NAME, c.table_schema,c.TABLE_NAME, c.DATA_TYPE+case when c.DATA_TYPE='varchar' then '('+convert(varchar,c.CHARACTER_MAXIMUM_LENGTH)+')' when c.DATA_TYPE='nvarchar' then '('+convert(varchar,c.CHARACTER_MAXIMUM_LENGTH)+')'  when c.DATA_TYPE='numeric' then '('+convert(varchar,c.NUMERIC_PRECISION)+','+convert(varchar,c.NUMERIC_SCALE)+')' else '' end,c.IS_NULLABLE, o.type
FROM INFORMATION_SCHEMA.COLUMNS c
left join sys.objects o
on c.TABLE_NAME=object_name(o.object_id)
WHERE c.TABLE_NAME ='OE_ORDER_HEADERS_ALL_CURRENT_KEYS'
and c.COLUMN_NAME ='ORDER_NUMBER'
order by c.COLUMN_NAME,c.TABLE_NAME