--Get All
Objects
select * from sys.all_objects
--Get all
table names
select * from sys.tables
--Get All
column information of a table
select * from information_schema.columns
where table_name
= 'TableName'
order by ordinal_position
--Description details for
sys.all_objects
1. name
Object name.
2. object_id
Object
identification number. Is unique within a database.
3. principal_id
ID of the
individual owner if different from the schema owner. By default,
schema-contained objects are owned by the schema owner. However, another owner
can be specified by using the ALTER AUTHORIZATION statement to change
ownership.
Is NULL if there
is no alternative individual owner.
Is NULL if the
object type is one of the following:
C = CHECK
constraint
D = DEFAULT
(constraint or stand-alone)
F = FOREIGN KEY
constraint
PK = PRIMARY KEY
constraint
R = Rule
(old-style, stand-alone)
TA = Assembly
(CLR) trigger
TR = SQL trigger
UQ = UNIQUE
constraint
4. schema_id
ID of the schema
that contains the object.
For all schema
scoped system objects that are included with SQL Server, this value is always
in (schema_id('sys'), schema_id('INFORMATION_SCHEMA')).
5. parent_object_id
ID of the object
to which this object belongs.
0 = Not a child
object.
6. type
Object type:
AF = Aggregate
function (CLR)
C = CHECK
constraint
D = DEFAULT (constraint
or stand-alone)
F = FOREIGN KEY
constraint
FN = SQL scalar
function
FS = Assembly
(CLR) scalar-function
FT = Assembly
(CLR) table-valued function
IF = SQL inline
table-valued function
IT = Internal
table
P = SQL Stored
Procedure
PC = Assembly
(CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY
constraint
R = Rule
(old-style, stand-alone)
RF =
Replication-filter-procedure
S = System base
table
SN = Synonym
SQ = Service queue
TA = Assembly
(CLR) DML trigger
TF = SQL
table-valued-function
TR = SQL DML
trigger
TT = Table type
U = Table
(user-defined)
UQ = UNIQUE
constraint
V = View
X = Extended
stored procedure
7. type_desc
Description of the
object type. AGGREGATE_FUNCTION
CHECK_CONSTRAINT
DEFAULT_CONSTRAINT
FOREIGN_KEY_CONSTRAINT
SQL_SCALAR_FUNCTION
CLR_SCALAR_FUNCTION
CLR_TABLE_VALUED_FUNCTION
SQL_INLINE_TABLE_VALUED_FUNCTION
INTERNAL_TABLE
SQL_STORED_PROCEDURE
CLR_STORED_PROCEDURE
PLAN_GUIDE
PRIMARY_KEY_CONSTRAINT
RULE
REPLICATION_FILTER_PROCEDURE
SYSTEM_TABLE
SYNONYM
SERVICE_QUEUE
CLR_TRIGGER
SQL_TABLE_VALUED_FUNCTION
SQL_TRIGGER
TABLE_TYPE
USER_TABLE
UNIQUE_CONSTRAINT
VIEW
EXTENDED_STORED_PROCEDURE
8. create_date
Date the object
was created.
9. modify_date
Date the object
was last modified by using an ALTER statement. If the object is a table or a
view, modify_date also changes when a clustered index on the table or view is
created or modified.
10. is_ms_shipped
Object created by
an internal SQL Server component.
11. is_published
Object is
published.
12. is_schema_published
Only the schema of
the object is published.
No comments:
Post a Comment