Create Procedure Sp_GetChinaname @tablename Varchar (50)
As Declare @SQL VARCHAR (1000), @a int, @b int, @i int, @J int, @M int, @n int, @ Yorn1 int, @ Yorn2 INT ---------- ----------------------------- - From the system to find Table, field
select 'Field Name' = name, 'Field Name' = type_name (xusertype), 'length' = length, 'Prec' = case when type_name (xtype) = 'uniqueidentifier' then xprec else OdbcPrec (xtype, length, xprec) end , 'Scale' = odbcscale (xtype, xscale), 'param_order' = colid, 'collation' = collation @system from syscolumns where id = Object_id (@tablename) and number = 0 Order by name
Select data table containing the field = name into #Columns from sysobjects where id in (select id From syscolumns where name = @ TableName) and xtype = 'U' Order By nameSelect @ m = Count (*) From #SystemSelect @ n = Count (*) from #COLUMNS -------------------------------------- EmpbasiF EXISTS (SELECT name FROM sysobjects WHERE name = N 'order data table lookup _userTable' AND type = 'U') Select @ YOrN1 = 1 Else Select @ YOrN1 = 0IF EXISTS (SELECT name FROM sysobjects WHERE name = N 'table retrieval _userTable Second order 'and type =' u ') SELECT @ YORN2 = 1 else SELECT @ YORN2 = 0 ---------------------------------------------------------------------------------------------------------- -------- SELECT @ SQL = 'SELECT * FROM Data Sheet Retrieval_USERTABLE First Order Where Part Name =' '' @ TableName '' 'SELECT @ SQL = @ SQL ' SELECT field order, primary key , Column name, Chinese name, data type, length, allow NULL, use from data table search _USERTABLE second-order WHERE object name = '' ' @ TableName ' '' f (@ Yorn1> 0) and (@ YORN2> 0)) Begin Select @ b = count (*) from (SELECT * FROM Data Sheet Search _USERTABLE Second Order Where Part Name = @ TableName) B SELECT @ i = @ b count (*) from (SELECT * FROM information Table Retriever_USERTABLE First Order Where Part Name = @ TableName) a SELECT @ J = Count (*) FRO M (Select * from FROM) _USERTABLE Second Order WHERE Rame Name Like '%' @ Tablename '%' AND Item Name In (Select Name from sysobjects where xtype = 'u')) c select @ a = @ J Count (*) from (SELECT * FROM Data Sheet Search _USERTABLE Second Order WHERE Chinese Name Like '%' @ Tablename '%' AND Item Name In (SELECT NAME FROM SYSOBJECTS WHERE Xtype = 'u')) D Endif @i>
0 Begin Exec (@SQL) if @B <@m select * from #system end else if @a> 0 begin select * from data table search _USERTABLE second-order WHERE column name Like '%' @ Tablename '%' and object name In (Select name from sysobjects where xtype = 'U') Select * from table retrieval _userTable Second Order Where Chinese name like '%' @ TableName '%' And the object name In (Select name from sysobjects where xtype = ' U ') if @J <@n select * from #columns Order by END ELSE BEGIN IF @m> 0 Select * from #system if @n> 0 Select * from #columns if (@ m @n) = 0 Print 'No related information, or is not in the current database!' end ---------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- Declare @objid int, - the object ID of the table @indid smallint, - the index id of an index @groupid smallint, - the filegroup id of an index @indname sysname, @groupname sysname, @status int, @keys nvarchar (2126), --Length ( 16 * MAX_IDENTIFIERLENGTH) (15 * 2) (16 * 3) @dbname sysname -------------------- -create table #spindtab (index_name sysname collate database_default NOT NULL, stats int, groupname sysname collate database_default NOT NULL, index_keys nvarchar (2126) collate database_default NOT NULL - see @keys above for length descr) -------- ------------ select @objid = Object_id (@tablename) ------------------ Declare MS_CRS_IND CURSOR LOCAL Static for Select Indid, GroupID, Name, Status from sysindexes where id = @objid and indid> 0 and Indid <255 and (status & 64) =
0 Order by Indid Open MS_CRS_IND FETCH MS_CRS_IND INTO @indid, @GroupID, @indname, @status --------------------- if @@ fetch_status <0 begin deallocate ms_crs_ind Raiserror (15472, -1, -1) - 'Object Does Not Have Any Indexes.' Return (0) End -------------------- Declare @K Int, @Thiskey nvarchar (131) - 128 3 Select @keys = index_col (@tablename, @indid, 1), @K = 2 if (@objid, @indid, 1, 'isdescending') = 1 ) Select @keys = @keys '(-)' select @thiskey = index_col (@tablename, @indid, @K) IF ((@Thiskey is not null) and (indexkey_property (@objid, @indid, @K, 'Isdescending') = 1)) Select @thiskey = @thiskey '(-)' While (@Thiskey is not null) begin select @keys = @keys ',' @Thiskey, @K = @k 1 Select @thiskey = index_col (@tablename, @indid, @K) IF ((@THISKEY is not null) and (indexkey_property (@objid, @indid, @K, 'isdescending') = 1)) SELECT @THISKEY = @ THISKEY '(-)' end select @group Name = groupname from sysfilegroups where groupid = @Groupid - INSERT ROW for index insert into #spindtab values (@indname, @status, @groupname, @keys) -------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- - Declare @empty varchar (1) SELECT @empty = '' declare @ des1 varchar (35), - 35 matches SPT_VALUES @ des2 varchar (35), @
Des4 varchar (35), @ des32 varchar (35), @ des48 varchar (35), @ des4096 varchar (35), @ des8388608 varchar (35), @ des16777216 varchar (35) SELECT @ DES1 = name from master.dbo.spt_values where type = 'I' and number = 1 select @ des2 = name from master.dbo.spt_values where type = 'I' and number = 2 select @ des4 = name from master.dbo.spt_values where type = 'I' and number = 4 select @ des32 = name from master.dbo.spt_values where type = 'I' and number = 32 select @ des64 = name from master.dbo.spt_values where type = 'I' and number = 64 select @ des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048 select @ des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096 select @ des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608 select @ des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216 select 'index name' = index_name, 'index descriptors' = Convert (VARCHAR (210), - Bits 16 Off, 1, 2, 16777216 ON, LOCATED ON GROUP CASE WHEN (Stats & 16) <> 0 TEN 'Clustered' Else 'Nonclustered' End Case When (Stats & 1) <> 0 THEN ',' @ des1 else @empty end case by (stats & 2) <> 0 TEN ',' @ des2 else @empty end case when (stats & 4) <> 0 Then ', ' @ des4 else @empty end
