Database Analysis Utility Script

Source: Internet
Author: User

--**************************************--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, 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, Logo= Case    when   ColumnProperty(,,'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,, number of bytes occupied=a.length, Length=ColumnProperty(,,'PRECISION'), number of decimal digits=IsNull(ColumnProperty(,,' 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 andD.xtype='U'<>'dtproperties'       Left  Joinsyscomments E Left JOINSys.extended_properties asEp onep.major_id=a.ID andEp.class=1'Aaclass'         --If you query only the specified table, add this condition  Order,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'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:,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 ' 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.