Function function: Get the list and type of the table according to the table name
When we are writing SQL programs, we will use some system built-in functions, such as right (), datalength (), etc.,,,,,,,,,,,,,,,,,,,,,,,,,,, It is necessary to write a function, as described below is a function of obtaining a structure.
Classification of functions:
1. Pure quantity: This type of function is only transmitted back to a single data value
2. Remove the function of the data set: Back to a Table Type Dating Data Set, divided into the following categories:
2.1 In-line data set functions: The content of the function is a SELECT statement
2.2 Multi-narrative function: The content of the function is composed of multiple SELECT statements
This example uses a multi-narrative function, its format:
Create function function_name
([@Parameter_name]: Incoming parameters)
Returns @return_variable table (relevant information on the table)
AS
Begin
Function_body
Return
end
Instance code:
Create Function TableStructure (@objname nvarchar (111)) ---- This is the name of the table to query ------------------------- -------------------
--fieldname is the name of the column of the table, TYPE is the type of column, the length of Length is the length of the column, NULL indicates whether it is allowed to be empty
Returns @Table Table (FieldName Varchar (50), Type Varchar (16), Length Int, Nullable Varchar (3)) ------------------------ ---------------------- Begin Declare @objid Int
--- 1. First from the table sysObjects to get the object identification code (ID) that matches the incoming table name (Name)
--SysObjects is a data column Select @ objid = ID from sysObjects where id = object_ID (@objname) )
--SysColumns is a data column that contains each data line in each data sheet and view
--- 2. Remove from the table syscolumns to match the target table identification code (ID), and is not the program project
(Column name, type, length, recorded record insert @table select 'fieldname' = name, 'type' = type_name (xusertype), 'length' = Convert (int, length), 'Nullable' = case when isnullable = 0 Then 'no' else 'yes' end from syscolumns where id = @ Objid and number = 0 Order by Colid Returnend
Liaodaibo
