Database Analysis Utility Script

--**************************************--Database Analysis Utility Script--**************************************--1 querying the database IDSelect db_id('Soft')--2 Querying the process ID of the connection databaseSelectHostname,spid,*  frommaster.dbo.sysprocesseswheredbidinch(Selectdbid fromMaster.dbo.sysdatabaseswhereName='soft2') andHostname like 'pc201710101756%'    --3 querying database for all table recordsSELECTA.name, B.rows fromsysobjects asAINNER JOINsysindexes asB ona.ID=b.idWHERE(A.type= 'u') and(B.indidinch(0,1))ORDER  byB.rowsDESC--4 Query table data dictionary full versionSELECTTable name= Case    whenA.colorder=1    ThenD.nameElse   "'  End, Field ordinal=a.colorder, field name=a.name, Logo= Case    when   ColumnProperty(A.id,a.name,'isidentity')=1    Then  '√'Else   "'  End, PRIMARY key= Case    when   exists(SELECT   1   fromsysobjectswhereXtype='PK'    andNameinch   (      SELECTName fromsysindexesWHEREIndidinch(      SELECTIndid fromSysindexkeysWHEREId=a.ID andColid=a.colid ))) Then  '√'  Else   "'   End, type=b.name, number of bytes occupied=a.length, Length=ColumnProperty(A.id,a.name,'PRECISION'), number of decimal digits=IsNull(ColumnProperty(A.id,a.name,' Scale'),0), allow null= Case    whenA.isnullable=1    Then  '√'Else   "'  End, the default value=IsNull(E.text,"'), field description=IsNull(ep.[value],"')       fromsyscolumns a Left  JoinSystypes b onA.xtype=B.xusertypeInner  Joinsysobjects D ona.ID=D.id andD.xtype='U'    andD.name<>'dtproperties'       Left  Joinsyscomments E onA.cdefault=e.id Left JOINSys.extended_properties asEp onep.major_id=a.ID andEp.class=1  andA.colorder=ep.minor_idwhereD.name='Aaclass'         --If you query only the specified table, add this condition  Order   byA.id,a.colorder--5 Query data dictionary simplified versionSELECTT.[name]  astable name, C.[name]  asfield name,cast(ep.[value]  as nvarchar( $)) as [Field Description]   fromSys.tables asTINNER JOINSys.columns asC onT.object_id =C.object_id   Left JOINSys.extended_properties asEp onep.major_id=C.object_id  andep.minor_id=c.column_idWHERE --Ep.class =1 andT.name='Aaclass'    --6 Querying the definition of all objects in a database--D: Default value, Fn:xx function, if:xx function, tf:xx function P: Stored procedure, TR: Trigger V: ViewSelectA.name,a.[type]B.[definition]  fromsys.all_objects a,sys.sql_modules bwherea.is_ms_shipped=0  andA.object_id =B.object_id --and A.[type] in (' P ', ' V ', ' AF ', ' FN ')--the type of object to query--and A.name= ' object name '--the object name to queryOrder  byA.[type] ASC--1. Name: Object Name--2. OBJECT_ID: Object identification number, which is unique in the data--3. PRINCIPAL_ID: Schema owner ID--4. parent_object_id: id,0 = Not child object of object to which this object belongs--5. Type: Object type, common type, AF = aggregate function P = SQL stored Procedure V = view TT = Table type U = table (user-defined type)--6. Type_desc: Description of the object type--7. Create_date/modify_date: Date created/Date Modified--8. Is_ms_shipped: Whether an object created by an internal SQL Server build is commonly used to determine whether a system built-in or user-defined object
 

Database Analysis Utility Script