How to view the columns of a system view in SQL Server?

Asked

Viewed 62 times

1

Well I come from Oracle culture and want to learn about SQL Server. I would like to know how I see the columns of a system view, for example: I tried with

sp_help teste.INFORMATION_SCHEMA.ROUTINES

and

desc teste.INFORMATION_SCHEMA.ROUTINES

to try to visualize the columns of this view that shows the routines of the test database but have not had any success, as I do the query of this meta data?

sql sql-server

  • the command showing the settings is sp_helptext, tried with him?

  • Didn’t work either.

  • I think @Ricardopunctual meant sp_help <nome da tabela/view> (I think in your case sp_help routines). I know little of the Oracle, but it seems he treats the schemas slightly different from Sql Server. If teste is the name of your schema, you can put it in front of the name so: sp_help teste.routines.

  • The sp_helptext would be to see the definition of view, store procedure, trigger...

1 answer

2

Try it this way (I’m guessing the name of VIEW is teste):

SELECT      AO.name AS [View Name]
        ,   AC.name AS [Column Name]
        ,   ST.name AS [Type]
FROM        sys.all_objects AO
INNER JOIN  sys.all_columns AC  ON  AC.object_id        = AO.object_id
INNER JOIN  sys.types       ST  ON  AC.system_type_id   = ST.system_type_id 
                                AND AC.user_type_id     = ST.user_type_id
WHERE       AO.type = 'V'
        AND AO.name = 'teste'
ORDER BY    AC.name

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.