Sql Server - View the View structure

Asked

Viewed 7,859 times

3

I executed the following commands in the Sql Server database:

comando 1:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'vendas'

resultado 1:
TABLE_CATALOG     TABLE_SCHEMA     TABLE_NAME      TABLE_TYPE    
----------------  ---------------  --------------  ------------- 
Empresa 1         dbo              vendas          VIEW

comando 2:
SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'vendas'

resultado 2:
TABLE_CATALOG     TABLE_SCHEMA     TABLE_NAME      VIEW_DEFINITION     CHECK_OPTION     IS_UPDATABLE    
----------------  ---------------  --------------  ------------------  ---------------  --------------- 
Empresa 1         dbo              vendas          (null)              NONE             NO

comando 3:
SELECT definition FROM sys.objects O JOIN sys.sql_modules M ON M.object_id = O.object_id WHERE O.object_id = object_id( 'dbo.vendas') AND O.type = 'V'

resultado 3:
definition    
------------- 
(null)

My doubts are

  • The table is really a view, or this is just a description for some other kind of table Material

  • How to get more information about this table if it is a view, which tables build it ?

  • The lack of definition information could be lack of user privilege, how to discover this ?

Edited:

To documentation of Microsoft shows that the column sys.sql_modules.definition may be encrypted.

1 answer

3


Table is even a view, or is this just a description for some other kind of Material table

Is a view yes. If it is a description, it will depend on its content.

How to get more information about this table if it is a view, which tables build it ?

Use the command sp_helptext vendas.

The lack of definition information could be lack of user privilege, how to discover this ?

Running the above command with a user without access permission will make SQL Server return an error message warning of this lack of permission.

  • I ran the trial but the return was >[Error] Script lines: 1-1 There is no text for object 'vendas'.

  • 1

    Could be any number of things. See the documentation here.

  • 1

    It is, it can be an encrypted definition: sys.sql_modules - "definition - NULL = Encrypted", I think I’ll have to wait for the return of the responsible for the bank, thanks for the help.

Browser other questions tagged

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