How to find list of columns which are "Read-only" in SQL Server?

huus2vyu 于 2023-10-15 发布在 SQL Server

关注(0)|答案(2)|浏览(151)

I want to find all columns of a table which have "read-only" access for some analysis.

how do can this be done in SQL Server?

I tried following but its not giving me desired results:

SELECT OBJECT_NAME(id) as ObjectName, Name as ComputedColumn
FROM syscolumns
WHERE COLUMNPROPERTY( id ,name, 'SystemDataAccess') = 0
AND name like 'my_table_name'

sql-server

来源：https://stackoverflow.com/questions/19842634/how-to-find-list-of-columns-which-are-read-only-in-sql-server

2条答案

按热度按时间

puruo6ea1#

If you mean "Read-Only" in the all users perspective (as in under normal circumstances nobody should be able to write to the column regardless of user specific access) the below type of thing could be used to return the columns that are identity or computed values (I can't think of any other specific cases where you cannot write to a column)...

DECLARE @Tbl nvarchar(500)='MyTableName';
SELECT T.Name, C.*
FROM sys.tables AS T
    JOIN sys.Columns AS C ON T.Object_id=C.object_id
WHERE T.Name=@Tbl
    AND (
        C.Is_Identity=1
        OR C.Is_Computed=1
    )

赞(0）回复(0）举报 2023-10-15

v1uwarro2#

A similar solution without the JOIN:

SELECT name FROM SYS.COLUMNS 
WHERE OBJECT_NAME(object_id) = 'MyTableName' 
    AND (is_identity = 1 OR is_computed = 1);

赞(0）回复(0）举报 2023-10-15

我来回答

How to find list of columns which are "Read-only" in SQL Server?

2条答案

相关问题

热门标签

最新问答