laika222
3/25/2018 - 5:43 PM
Searching and Describing Objects from Object Explorer, sp_help, sp_columns
-- rather than searching through object explorer, you can query sys.objects to find objects that have certain things in the name. This shows the name, which you can then find in Object Explorer. The type_desc will tell you whether the object is a table, stored procedure, view, etc.
SELECT * FROM sys.objects
WHERE name LIKE '%LVS%contribution%'
/* Results that you can then look up in Object Explorer:
name object_id principal_id schema_id parent_object_id type type_desc
c_convert_contribution 1398348096 NULL 1 0 U USER_TABLE
LRP_CSO_DEV_CONTRIBUTIONS 396020642 NULL 1 0 P SQL_STORED_PROCEDURE
LV_QB_Contribution 1991118284 NULL 1 0 V VIEW
LVS_CONTRIBUTION 2056602615 NULL 1 0 V VIEW
*/
-- you can describe an oject by using sp_help. This will return multiple result sets that show you different information about that object, such as the name, column names and types, identities, indexes, constraints, foreign key references. You can use this on tables, views, functions, etc.
EXEC sp_help T_CONTRIBUTION
/* Results:
Name Owner Type Created_datetime
T_CONTRIBUTION dbo user table 2000-08-11 20:56:35.483
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
ref_no int no 4 10 0 no (n/a) (n/a) NULL
customer_no int no 4 10 0 no (n/a) (n/a) NULL
cont_dt datetime no 8 yes (n/a) (n/a) NULL
recd_amt money no 8 19 4 yes (n/a) (n/a) NULL
cont_amt money no 8 19 4 yes (n/a) (n/a) NULL
cont_type char no 1 yes yes yes SQL_Latin1_General_CP1_CI_AS
campaign_no int no 4 10 0 yes (n/a) (n/a) NULL
appeal_no int no 4 10 0 yes (n/a) (n/a) NULL
media_type int no 4 10 0 yes (n/a) (n/a) NULL
source_no int no 4 10 0 yes (n/a) (n/a) NULL
solicitor char no 8 yes yes yes SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
No identity column defined. NULL NULL NULL
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
index_name index_description index_keys
IND_t_contribution_appeal_no nonclustered located on PRIMARY appeal_no
IND_t_contribution_batch_no nonclustered located on PRIMARY batch_no
IND_t_contribution_campaign_no nonclustered located on PRIMARY campaign_no
IND_T_CONTRIBUTION_customer_no nonclustered located on PRIMARY customer_no
IND_T_CONTRIBUTION_initiator_no nonclustered located on PRIMARY initiator_no
IND_t_contribution_mi_lock nonclustered located on PRIMARY mir_lock
IND_T_CONTRIBUTION_plan_no nonclustered located on PRIMARY plan_no
IND_T_CONTRIBUTION_worker_customer_no nonclustered located on PRIMARY worker_customer_no
PK_T_CONRIBUTION clustered, unique, primary key located on PRIMARY ref_no
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
DEFAULT on column cont_amt DF_T_CONTRIBU_cont_amt_1__10 (n/a) (n/a) (n/a) (n/a) (0)
DEFAULT on column recd_amt DF_T_CONTRIBU_recd_amt_2__10 (n/a) (n/a) (n/a) (n/a) (0)
DEFAULT on column create_loc DF_T_CONTRIBUTION_create_loc (n/a) (n/a) (n/a) (n/a) ([dbo].[fs_location]())
DEFAULT on column created_by DF_T_CONTRIBUTION_created_by (n/a) (n/a) (n/a) (n/a) ([dbo].[fs_user]())
DEFAULT on column last_update_dt DF_T_CONTRIBUTION_last_update_dt (n/a) (n/a) (n/a) (n/a) (getdate())
DEFAULT on column last_updated_by DF_T_CONTRIBUTION_last_updated_by (n/a) (n/a) (n/a) (n/a) ([dbo].[fs_user]())
FOREIGN KEY FK_T_CONT_TR_SALES_CHANNEL No Action No Action Enabled Is_For_Replication channel
REFERENCES Impresario.dbo.TR_SALES_CHANNEL (id)
FOREIGN KEY FK_T_CONTRIBUTION_t_campaign No Action No Action Enabled Is_For_Replication campaign_no
REFERENCES Impresario.dbo.T_CAMPAIGN (campaign_no)
FOREIGN KEY FK_T_CONTRIBUTION_T_CUSTOMER_initiator_no No Action No Action Enabled Is_For_Replication initiator_no
REFERENCES Impresario.dbo.T_CUSTOMER (customer_no)
FOREIGN KEY FK_T_CONTRIBUTION_T_CUSTOMER_worker_customer_no No Action No Action Enabled Is_For_Replication worker_customer_no
REFERENCES Impresario.dbo.T_CUSTOMER (customer_no)
FOREIGN KEY FK_T_CONTRIBUTION_t_fund No Action No Action Enabled Is_For_Replication fund_no
REFERENCES Impresario.dbo.T_FUND (fund_no)
FOREIGN KEY FK_T_CONTRIBUTION_T_PLAN_plan_no No Action No Action Enabled Is_For_Replication plan_no
REFERENCES Impresario.dbo.T_PLAN (plan_no)
PRIMARY KEY (clustered) PK_T_CONRIBUTION (n/a) (n/a) (n/a) (n/a) ref_no
DEFAULT on column create_dt T_CONTRIBUTION_DFd_create_dt (n/a) (n/a) (n/a) (n/a) (getdate())
FOREIGN KEY t_contribution_FK_batch_no No Action No Action Enabled Is_For_Replication batch_no
REFERENCES Impresario.dbo.T_BATCH (batch_no)
FOREIGN KEY T_CONTRIBUTION_FK_customer_no No Action No Action Enabled Is_For_Replication customer_no
REFERENCES Impresario.dbo.T_CUSTOMER (customer_no)
FOREIGN KEY t_contribution_FK_media_type No Action No Action Enabled Is_For_Replication media_type
REFERENCES Impresario.dbo.TR_MEDIA_TYPE (id)
Table is referenced by foreign key
Impresario.dbo.TX_CONT_EVEX: FK_TX_CONT_EVEX_cont_ref_no
Impresario.dbo.TX_CONT_MEMB: FK_TX_CONT_MEMB_cont_ref_no
*/
-- you can also use sp_columns to get a quick description of the columns and data types.
EXEC sp_columns lvs_soft_credit
/*Results:
TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH SCALE RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SS_DATA_TYPE
Impresario dbo lvs_soft_credit ref_no 4 int 10 4 0 10 0 NULL NULL 4 NULL NULL 1 NO 56
Impresario dbo lvs_soft_credit customer_no 4 int 10 4 0 10 1 NULL NULL 4 NULL NULL 2 YES 38
Impresario dbo lvs_soft_credit cont_dt 11 datetime 23 16 3 NULL 1 NULL NULL 9 3 NULL 3 YES 111
Impresario dbo lvs_soft_credit recd_amt 3 money 19 21 4 10 1 NULL NULL 3 NULL NULL 4 YES 110
Impresario dbo lvs_soft_credit cont_amt 3 money 19 21 4 10 1 NULL NULL 3 NULL NULL 5 YES 110